Summing a column based on two criteria in other columns

[nathan2314]

Hey All :)
I have a spreadsheet that I'm trying to sum certain values in column E. But I only want to add the numbers if they meet two criteria. One is that the value in column A is a certain month (these values are 1 through 12) and the other criteria is if the year in column C is a certain value (2007 or 2008). I've figured out that I can do it for one criteria using the sumif() function ( so for example for the month of September : sumif(A:A,9,E:E) will work) but this only takes into account the month. How can I also include to check the year in column C. I would want to sum those for 2007 and 2008 separately in different cells ??

Appreciate any help!

[Bob Phillips]

=SUMPRODUCT(--(A2:A200=1),--(C2:C200=2008),E2:E200)

sumproduct needs an explicit range, not a full column, in Excel 2003 and prior

[nathan2314]

Hey that works like a charm! :clap:
what does using the -- mean in sumproduct.?

THANKS!!:yes

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.

[Simon Lloyd]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.Bob, i haven't quite finished reading it or creating my own examples but it's fantastic, you can trawl the internet or read as many books as you like but they don't switch the light on like that, i have used SUMPRODUCT for evaluating 2 ranges and anything further has always been supplied by yourself (or sometimes others) but i have never fully understood how it works - that explanation is like a breath of fresh air! :clap:

[Bob Phillips]

Thanks Simon. I know what you mean by not getting good examples. Array formulae are the best example, you always see teh simple example and then can't go any further. There is a good book in there ... or perhaps my Power Excel course <vbg>.

BTW it is in the throes of a rewrite, splitting up, clarifying some points, explaining some better, but unfortunately, time is difficult to find.

[Simon Lloyd]

Bob, i seldom use array formula simply because i don't know when or sometimes how, SUMPRODUCT seems to make the process easier (although still no clarity until reading your explanation), never mind can't find good examples you cant find any that you can comprehend because they have been written by someone who knows what they are talking about not someone who can explain what they are talking about! seldom is the user able to teach!

[Bob Phillips]

Simon, SUMPRODUCT, even though not array entered, is still an array formula. All the things in that paper can apply to array entered formulae as well, but you would use the * operator not the double unary --.

[nathan2314]

Yes Definitely Great stuff on sumproduct!

But now i have another issue with needing to use other several other criteria in which to sum numbers. So far i have 48 cells that have to be populated with sums based on unique criteria. First I just had to determine the month in column A and then the Year in column C and then if they met the criteria I would sum all numbers in column F. I was able to make all that work following the sumproduct logic -->


=SUMPRODUCT(--('RFF Data 179'!$A$2:$A$500=1),--(YEAR('RFF Data 179'!$C$2:$C$500)=2008),'RFF Data 179'!$F$2:$F$500)"

Now this is just for the 1st of 48 cells. Namely the sum of all in column F for month=january and year=2008. I have 1 cell for each month/year combination = 48 cells total --> january - december for 2008/2009 = 48 cells.
:banghead: Ok so now the problem:
I now have a drop down list that a user can select with 15 values ('O2', 'O3', O4, O5...). Now for each value in this list, I need to look at a 4th column in my data sheet to add this criteria for summing across all those 48 cells. So for the example above for the month=january and year=2008 cell..when a user selects 'O2' then that cell would need month=january, year=2008, and list="O2" and sum those cells in column F that met these. But if a user selects "O3" then that cell needs month=january, year=2008, and list-"O3"....And this needs to be done for all 48 cells ???

Whats the best way to do this....I guess i could try to write a looooonngg sumproducts with 15 if() or conditions but there has got to be a better way ??

Help!:dunno

[nathan2314]

I guess I could just do 15 sets of 48 cells on other spreadsheet and then pull in whichever 48 cells corresponds to what the user selects out of the 15 choices in the drop down list. But that would be alot of formula writing and pasting.
Is there maybe somekind of function where i could just list all 15 choices and put that in the 48 formulas I already have. Then the whole formula would select whichever choice the user made and then look for that value in the column of data??

Seems like there should be an easier way than writing a whole bunch of formulas in another spreadsheet ??

:doh:

[figment]

if you could post a sample worksheet then we could get a better idea of what you are asking. but it sounds like you simply need to make your range dependent on another cells output. this could probably be done with named ranges, or the indirect() command.

[Bob Phillips]

just add an extra condition testing the value in the data validation cell.