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

Question

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?

brettdj · Answer 1 · 2012-01-09 01:55:54Z

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

soandos · Answer 2 · 2012-01-09 03:06:22Z

up vote 1 down vote

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.

answered Jan 9 at 1:41

soandos
9,51931738

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

feedback

F106dart · Answer 3 · 2012-01-11 13:58:27Z

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

3 Answers