Sumif Amounts Based on Months for Multiple Months

0
votes

I have excel data in the following format

Date         Amount
01-Jan-16     23.94
12-Jan-16     17.96
26-Jan-16     32.92
03-Feb-16     38.90
20-Feb-16     62.27
26-Feb-16     45.89

I would like to sum the amount field for specific months, that is I would like to sum up the amount in January and also in February separately.

This is the code I have tried thus far which I found in a previously asked question (see Excel Formula to SUMIF date falls in particular month).

=SUM(IF(MONTH(A:A)=1,B:B,0))

It works for January but when I change 1 to 2 it returns zero.

=SUM(IF(MONTH(A:A)=2,B:B,0))

Would sumifs work better?

2
excelsumifs

2 Answers

3
votes

Here are two other ways of doing it:-

=SUMPRODUCT((MONTH(A2:A10)=1)*B2:B10)

and

=SUMIFS(B2:B10,A2:A10,">="&DATE(2016,1,1),A2:A10,"<"&DATE(2016,2,1))

Change as necessary to get totals for other months.

I have put the results next to the original array formula in this screen shot:-

enter image description here

2
votes

Use specific range rather than full column. You must use the formula as array formula.

=SUM(IF(MONTH($A$2:$A$1000)=1,$B$2:$B$1000,0))

Press CTRL+SHIFT+ENTER to evaluate the formula as array formula.