Sum
 

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.

Sum
 

=SUMIF(A:A,C1,B:B)
assuming cell C1 holds the value b
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Rod" wrote in message
...
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.

Sum
 

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

change range to fit your needs

"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.

Sum
 

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.

Sum
 

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

"Eduardo" wrote:

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

change range to fit your needs

"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.

Sum
 

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.

Sum
 

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;FAL SE;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" wrote in message
...
Thank you so much
If you don't mind could you please explain briefly the logic behind it?
Thanks in advance.

Sum
 

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;FAL SE;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" wrote in message
...
Thank you so much
If you don't mind could you please explain briefly the logic behind it?
Thanks in advance.


.