Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM all Instances Q
How could I achieve the following via formula
I want to add up all the instances that certain numbers appear in Col B with their corresponding values in COL D e.g. if 1 or 3 or 6 or 20 or 26 or 30 or 55 appears in Row1 then add the value that appears in Row1 COL D and do this for every row in sheet where a values exist in COL B. I know how I could do it if I was looking for only 1 instance in ColB, but the multi instances has me guessing Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM all Instances Q
You want Sumif, as in:
=sumif(B:B,1,D:D) Regards, Fred "Seanie" wrote in message ... How could I achieve the following via formula I want to add up all the instances that certain numbers appear in Col B with their corresponding values in COL D e.g. if 1 or 3 or 6 or 20 or 26 or 30 or 55 appears in Row1 then add the value that appears in Row1 COL D and do this for every row in sheet where a values exist in COL B. I know how I could do it if I was looking for only 1 instance in ColB, but the multi instances has me guessing Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM all Instances Q
Thanks, that would be ideal if I could do =SUMIF(B:B,1,2,3,4,5,D:D),
but it only takes 1 criteria |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM all Instances Q
If the ,1,2,3,4,5 part means sum them if value in B equals any one of those 5
values, then simply write a longish formula for all cases: =SUMIF(B:B,1,D:D)+SUMIF(B:B,2,D:D)+SUMIF(B:B,3,D:D )+SUMIF(B:B,4,D:D)+SUMIF(B:B,5,D:D) You only have to do it once. "Seanie" wrote: Thanks, that would be ideal if I could do =SUMIF(B:B,1,2,3,4,5,D:D), but it only takes 1 criteria . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM all Instances Q
Is there any limit to the 'longish' formula? Something tells me 8 is
the limit |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM all Instances Q
Not in this case. The limit is the number of characters that can make up a
formula. That is 1024 in Excel 2003. You are thinking of nested formulas when you mention 8. It's actually 7 levels of nesting, which gives you 8 options. "Seanie" wrote: Is there any limit to the 'longish' formula? Something tells me 8 is the limit . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM all Instances Q
Another:
=SUM(SUMIF(B:B,{1,2,3,4,5},D:D)) Seanie wrote: Thanks, that would be ideal if I could do =SUMIF(B:B,1,2,3,4,5,D:D), but it only takes 1 criteria -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM all Instances Q
Dadgummit! I almost went there. Stopped before adding the outside SUM(), it
didn't work as an array formula, so I went with the individual SUMIF()s. Hope OP sees your post. "Dave Peterson" wrote: Another: =SUM(SUMIF(B:B,{1,2,3,4,5},D:D)) Seanie wrote: Thanks, that would be ideal if I could do =SUMIF(B:B,1,2,3,4,5,D:D), but it only takes 1 criteria -- Dave Peterson . |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM all Instances Q
Thanks guys
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM all Instances Q
A further twist on =SUM(SUMIF(B:B,{1,2,3,4,5},D:D))
How could I incorporate a further criteria eg. If ColA contains "London" and if ColB contains one of 1,2,3,4,5 then add up all those instance in ColD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting % of Instances | Excel Worksheet Functions | |||
Excel in Two instances | Excel Discussion (Misc queries) | |||
3 Instances | Excel Discussion (Misc queries) | |||
linking certain instances | Excel Discussion (Misc queries) | |||
Create list of unique instances from list of multiple Instances | Excel Worksheet Functions |