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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Rod" <R...@discussions.microsoft.com> wrote in message
news:D68E252D-EF7E-439E...@microsoft.com...

[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
--
Max
Singapore
---

[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
--
Max
Singapore

"Rod" <R...@discussions.microsoft.com> wrote in message

news:FD29FCF2-CF0F-4750...@microsoft.com...

[Rod]

Thanks a lot

"Max" wrote:

> .
>

[mpima]

How do I sum a row of cells w/ numbers and letters (various) in each cell?
--
mpima

[Gary''s Student]

Use =SUM()

for example if A1 thru F1 contains:

1 2 3 qwerty 5 67

then SUM(A1:F1) returns 78
qwerty is treated like a zero.
--
Gary''s Student - gsnu200909