| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
