Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF formulas
Hi there
I'm trying to get the following fomula to work. =SUMIF('Actuals YTD'!$A$2:$A$630,"=5010111",'Actuals YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5010211",'Actuals YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5120111", 'Actuals YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5200111",'Actuals YTD'!$C$2:$N$630) It doesn't appear to be picking up the figures in any other column other than "C", when it should be adding up from "C" to "N". A SUMPRODUCT doesn't work due to the fact the data I'm searching on doesn't run in sequence. Any help greatly appreciated!! Thanks Caroline |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF formulas
Lightly tested ok, think you could try something like this:
=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{555,333,444},0) )*B2:C10) Adapt the ranges & the criteria ie the nums within the {...} bit, to suit As for your obs on SUMIF, it works only for a single sum col, ie the leftmost col C in your expression, albeit Excel seems to happily accept the multi-col, thus giving you the false impression that it works that way. The above sumproduct should provide you with a concise way to arrive at the expected results. Success? hit the YES below -- Max Singapore xde --- "Caroline" wrote: I'm trying to get the following fomula to work. =SUMIF('Actuals YTD'!$A$2:$A$630,"=5010111",'Actuals YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5010211",'Actuals YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5120111", 'Actuals YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5200111",'Actuals YTD'!$C$2:$N$630) It doesn't appear to be picking up the figures in any other column other than "C", when it should be adding up from "C" to "N". A SUMPRODUCT doesn't work due to the fact the data I'm searching on doesn't run in sequence. Any help greatly appreciated!! Thanks Caroline |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF formulas
Just a thought, if you had another column that added up the information in
columns C-N and you could use your current formula and access the "new" column of numbers... -- Wag more, bark less "Caroline" wrote: Hi there I'm trying to get the following fomula to work. =SUMIF('Actuals YTD'!$A$2:$A$630,"=5010111",'Actuals YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5010211",'Actuals YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5120111", 'Actuals YTD'!$C$2:$N$630)+SUMIF('Actuals YTD'!$A$2:$A$630,"=5200111",'Actuals YTD'!$C$2:$N$630) It doesn't appear to be picking up the figures in any other column other than "C", when it should be adding up from "C" to "N". A SUMPRODUCT doesn't work due to the fact the data I'm searching on doesn't run in sequence. Any help greatly appreciated!! Thanks Caroline |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct and Sumif formulas | Excel Discussion (Misc queries) | |||
sumif - using cells with formulas | Excel Discussion (Misc queries) | |||
sumif formulas | Excel Worksheet Functions | |||
SumIf formulas. | Excel Discussion (Misc queries) | |||
sumif and array formulas | Excel Worksheet Functions |