Sum
in [General]
|
From: Rod on 4 Jan 2010 12:01 Thank you so much If you don't mind could you please explain briefly the logic behind it? Thanks in advance. "Max" wrote: > In D1: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,C1:C2,0))),B1:B10) > where C1:C2 houses the elements, eg: b, d (as you posted) > Success? hit the YES below > -- > Max > Singapore > --- > "Rod" wrote: > > Hi, I have data as shown below. > > Col-A Col-B Col-C Col-D > > a 10 b > > b 15 d > > c 20 > > d 25 > > e 30 > > I need the sum of "b" and "d" values(15+25) in column D1(40). If I add the > > any letter of Col-A in column c it will automatically update the value in D1. > > Could any one help me please. Thanks in advance.
From: Max on 4 Jan 2010 18:11 MATCH(A1:A10,C1:C2,0) exact matches every element in A1:A10 with that in C1:C2 and returns a resulting col array like this: {#N/A;1;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A} where #N/A = no match, numbers 1, 2 = the relative positions where the match is found ISNUMBER(MATCH(A1:A10,C1:C2,0)) then converts it to True/False: {FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} where #N/A = False, any number (ie the 1, 2) = True The double minus: -- --(ISNUMBER(MATCH(...))) then converts the True/Falses to 1/0: {0;1;0;1;0;0;0;0;0;0} The sumproduct then cross-multiplies the above final array of 1/0s with the corresponding numbers in B1:B10 and sums the lot, giving the desired result -- Max Singapore "Rod" <Rod(a)discussions.microsoft.com> wrote in message news:FD29FCF2-CF0F-4750-BE1C-9BF5112B20DD(a)microsoft.com... > Thank you so much > If you don't mind could you please explain briefly the logic behind it? > Thanks in advance.
From: Rod on 5 Jan 2010 10:07
Thanks a lot "Max" wrote: > MATCH(A1:A10,C1:C2,0) > exact matches every element in A1:A10 with that in C1:C2 > and returns a resulting col array like this: > {#N/A;1;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A} > where #N/A = no match, > numbers 1, 2 = the relative positions where the match is found > > ISNUMBER(MATCH(A1:A10,C1:C2,0)) > then converts it to True/False: > {FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} > where #N/A = False, any number (ie the 1, 2) = True > > The double minus: -- > --(ISNUMBER(MATCH(...))) > then converts the True/Falses to 1/0: > {0;1;0;1;0;0;0;0;0;0} > > The sumproduct then cross-multiplies the above final array of 1/0s with the > corresponding numbers in B1:B10 and sums the lot, giving the desired result > -- > Max > Singapore > "Rod" <Rod(a)discussions.microsoft.com> wrote in message > news:FD29FCF2-CF0F-4750-BE1C-9BF5112B20DD(a)microsoft.com... > > Thank you so much > > If you don't mind could you please explain briefly the logic behind it? > > Thanks in advance. > > > . > |