# Excel formula for “select sum(B) where month(A)=3”

I have excel data as follows:

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?

-

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)`

-
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.

-
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.

-