7

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?

Improve this question

3 Answers 3

Reset to default
5

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

Improve this answer
1
  • Brilliant thankyou. This did exactly what I needed.
    – elMarquis
    Jan 10, 2012 at 20:29
1

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.

Improve this answer
5
  • 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, 2012 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, 2012 at 3:07
  • It was in the dropbox link which has since been replaced by a sample picture
    – brettdj
    Jan 9, 2012 at 5:12
  • @brettdj, guess I just missed it then. The answer works now though.
    – soandos
    Jan 9, 2012 at 5:13
  • I had meant to remove my downvote after your updated answer a few hours back, now done.
    – brettdj
    Jan 9, 2012 at 8:51
0

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

Improve this answer

You must log in to answer this question.

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