Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum serie with condition
Hi!
I have A B C D E F 1 Name Value Name Value Name Value 2 A 6 B 5 B 7 3 B 7 C 9 A 8 4 C 9 A 7 A 7 5 A 8 A 6 C 6 I would like Sum all values in block A2:F5 where corresponding Name is A In this case: A=42 For another words: Sum(B2:B5) where (A2:A5)=A + Sum(D2:D5) where (C2:C5)=A+ Sum(F2:F5) where (E2:E5)=A Thanks in advance. an |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum serie with condition
=SUMPRODUCT((A2:A5="A")*(B2:F5))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "an" wrote in message ... Hi! I have A B C D E F 1 Name Value Name Value Name Value 2 A 6 B 5 B 7 3 B 7 C 9 A 8 4 C 9 A 7 A 7 5 A 8 A 6 C 6 I would like Sum all values in block A2:F5 where corresponding Name is A In this case: A=42 For another words: Sum(B2:B5) where (A2:A5)=A + Sum(D2:D5) where (C2:C5)=A+ Sum(F2:F5) where (E2:E5)=A Thanks in advance. an |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum serie with condition
Sorry Bob but return #VALUE!
Thanks. an "Bob Phillips" wrote: =SUMPRODUCT((A2:A5="A")*(B2:F5)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "an" wrote in message ... Hi! I have A B C D E F 1 Name Value Name Value Name Value 2 A 6 B 5 B 7 3 B 7 C 9 A 8 4 C 9 A 7 A 7 5 A 8 A 6 C 6 I would like Sum all values in block A2:F5 where corresponding Name is A In this case: A=42 For another words: Sum(B2:B5) where (A2:A5)=A + Sum(D2:D5) where (C2:C5)=A+ Sum(F2:F5) where (E2:E5)=A Thanks in advance. an |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum serie with condition
Try this:
=SUMIF(A2:E5,"A",B2:F5) Note how the ranges are offset. -- Biff Microsoft Excel MVP "an" wrote in message ... Hi! I have A B C D E F 1 Name Value Name Value Name Value 2 A 6 B 5 B 7 3 B 7 C 9 A 8 4 C 9 A 7 A 7 5 A 8 A 6 C 6 I would like Sum all values in block A2:F5 where corresponding Name is A In this case: A=42 For another words: Sum(B2:B5) where (A2:A5)=A + Sum(D2:D5) where (C2:C5)=A+ Sum(F2:F5) where (E2:E5)=A Thanks in advance. an |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum serie with condition
Ok, TV.
Work fine. Thank you very much. an "T. Valko" wrote: Try this: =SUMIF(A2:E5,"A",B2:F5) Note how the ranges are offset. -- Biff Microsoft Excel MVP "an" wrote in message ... Hi! I have A B C D E F 1 Name Value Name Value Name Value 2 A 6 B 5 B 7 3 B 7 C 9 A 8 4 C 9 A 7 A 7 5 A 8 A 6 C 6 I would like Sum all values in block A2:F5 where corresponding Name is A In this case: A=42 For another words: Sum(B2:B5) where (A2:A5)=A + Sum(D2:D5) where (C2:C5)=A+ Sum(F2:F5) where (E2:E5)=A Thanks in advance. an |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum serie with condition
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "an" wrote in message ... Ok, TV. Work fine. Thank you very much. an "T. Valko" wrote: Try this: =SUMIF(A2:E5,"A",B2:F5) Note how the ranges are offset. -- Biff Microsoft Excel MVP "an" wrote in message ... Hi! I have A B C D E F 1 Name Value Name Value Name Value 2 A 6 B 5 B 7 3 B 7 C 9 A 8 4 C 9 A 7 A 7 5 A 8 A 6 C 6 I would like Sum all values in block A2:F5 where corresponding Name is A In this case: A=42 For another words: Sum(B2:B5) where (A2:A5)=A + Sum(D2:D5) where (C2:C5)=A+ Sum(F2:F5) where (E2:E5)=A Thanks in advance. an |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
max data serie | Charts and Charting in Excel | |||
Hide and unhide a serie | Charts and Charting in Excel | |||
Format serie of numbers | Excel Worksheet Functions | |||
Filling / Extending serie | Excel Discussion (Misc queries) | |||
Second serie doesn't use X-as values | Charts and Charting in Excel |