CODEWITHSUNDEEP
excel
logan
logan

Reputation: 8346

Excel Formula to SUMIF date falls in particular month

I have excel data in following format.

Date        Amount

03-Jan-13   430.00 
25-Jan-13   96.00 
10-Jan-13   440.00 
28-Feb-13   72.10 
28-Feb-13   72.30

I need to sum the amount field only if the month lies in Jan Month.

What i have tried is ,

=SUMIF(A2:A6,"MONTH(A2:A6)=1",B2:B6)

But it returns, 

0

What i need is,

Following values to be summed, 430.00 + 96.00 + 440.00 = 966.00

Upvotes: 45

Views: 308292

Answers (3)

chuff
chuff

Reputation: 5866

Try this instead:

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

It's an array formula, so you will need to enter it with the Control-Shift-Enter key combination.

Here's how the formula works.

  1. MONTH($A$2:$A$6) creates an array of numeric values of the month for the dates in A2:A6, that is,
    {1, 1, 1, 2, 2}.
  2. Then the comparison {1, 1, 1, 2, 2}= 1 produces the array {TRUE, TRUE, TRUE, FALSE, FALSE}, which comprises the condition for the IF statement.
  3. The IF statement then returns an array of values, with {430, 96, 400.. for the values of the sum ranges where the month value equals 1 and ..0,0} where the month value does not equal 1.
  4. That array {430, 96, 400, 0, 0} is then summed to get the answer you are looking for.

This is essentially equivalent to what the SUMIF and SUMIFs functions do. However, neither of those functions support the kind of calculation you tried to include in the conditional.

It's also possible to drop the IF completely. Since TRUE and FALSE can also be treated as 1 and 0, this formula--=SUM((MONTH($A$2:$A$6)=1)*$B$2:$B$6)--also works.

Heads up: This does not work in Google Spreadsheets

Upvotes: 61

Marco Demaio
Marco Demaio

Reputation: 34407

=SUMPRODUCT( (MONTH($A$2:$A$6)=1) * ($B$2:$B$6) )

Explanation:

  • (MONTH($A$2:$A$6)=1) creates an array of 1 and 0, it's 1 when the month is january, thus in your example the returned array would be [1, 1, 1, 0, 0]

  • SUMPRODUCT first multiplies each value of the array created in the above step with values of the array ($B$2:$B$6), then it sums them. Hence in your example it does this: (1 * 430) + (1 * 96) + (1 * 440) + (0 * 72.10) + (0 * 72.30)

This works also in OpenOffice and Google Spreadsheets

Upvotes: 19

Riff Ron
Riff Ron

Reputation: 261

=Sumifs(B:B,A:A,">=1/1/2013",A:A,"<=1/31/2013")

The beauty of this formula is you can add more data to columns A and B and it will just recalculate.

Upvotes: 26

Related Questions