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


for April

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:


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 .