Excel formula for "select sum(B) where month(A)=3"

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?

elMarquis

Posted 2012-01-09T01:35:59.573

Reputation: 403

Answers

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

brettdj

Posted 2012-01-09T01:35:59.573

Reputation: 1 912

Brilliant thankyou. This did exactly what I needed. – elMarquis – 2012-01-10T20:29:11.570

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.

soandos

Posted 2012-01-09T01:35:59.573

Reputation: 22 744

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 – 2012-01-09T01:57:11.713

@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 – 2012-01-09T03:07:04.867

It was in the dropbox link which has since been replaced by a sample picture – brettdj – 2012-01-09T05:12:22.207

@brettdj, guess I just missed it then. The answer works now though. – soandos – 2012-01-09T05:13:43.713

I had meant to remove my downvote after your updated answer a few hours back, now done. – brettdj – 2012-01-09T08:51:29.090

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

F106dart

Posted 2012-01-09T01:35:59.573

Reputation: 1 713