Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

up vote 3 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

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.