Sum

[Rod]

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.

 

[Bernard Liengme]

=SUMIF(A:A,C1,B:B)
assuming cell C1 holds the value b
best wishes

 

[Eduardo]

Hi,
in D1 enter
=sumproduct(--(C1=$a$1:$A$1000),$B$1:$B$1000)

change range to fit your needs

 

[Max]

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

 

[Rod]

Thanks for quick response, when I enter this formula in D1 i am getting just
15 only

 

[Rod]

Thank you so much

If you don't mind could you please explain briefly the logic behind it?

Thanks in advance.

 

[Max]

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

 

[Rod]

Thanks a lot

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



.