Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have excel data as follows:

enter image description here

I want show the Total Cost for each month. I can achieve this in Google Spreadsheets with the following formula:

=INDEX(QUERY(COSTS!$1:$1101,"select sum(B) where month(A)=3"), 2)

Which gets the result for April.

But my requirement is now to do the same in Excel (which doesn't support the QUERY statements).

I have been playing around with SUMIF, but can't figure out how to make it work and not even sure if it's the right approach.

Does anyone know how I can do this?

share|improve this question
up vote 4 down vote accepted

As column A is a date you will need something like this to test that the month is March(3), April(4) etc to match it, below is one way

ie for March

=SUMPRODUCT(--(MONTH($A$1:$A$50)=3),$B$1:$B$50)

for April
=SUMPRODUCT(--(MONTH($A$1:$A$50)=4),$B$1:$B$50)

enter image description here

share|improve this answer
    
Brilliant thankyou. This did exactly what I needed. – elMarquis Jan 10 '12 at 20:29

I would first create an intermediary column containing the dates (=Month(a1) and copy down column B.

Then use the following:

=SumIf(b1:b1101,"=4",c1:c1101)

This assumes that the B column is where the month is held, and that the month is equal to 4.

share|improve this answer
    
The sample file shows that the column A data is a date - column A doesn't contain a 3 it contains 12\03\2011 etc. So your approach above won't work. – brettdj Jan 9 '12 at 1:57
    
@brettdj, When I posted this answer, there was no sample (hence the comment in my answer). Thanks for letting me know information had been added. – soandos Jan 9 '12 at 3:07
    
It was in the dropbox link which has since been replaced by a sample picture – brettdj Jan 9 '12 at 5:12
    
@brettdj, guess I just missed it then. The answer works now though. – soandos Jan 9 '12 at 5:13
    
I had meant to remove my downvote after your updated answer a few hours back, now done. – brettdj Jan 9 '12 at 8:51

Similar to @soandos' approach , I would also use a helper column, and then build a pivot table with it.

You can use @soandos formula for the helper column, or you can use one like I've shown in the screen shot below.

enter image description here

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .