Excel Formula to SUMIF date falls in particular month
excel
Solution
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.
- 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}`.
- 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.
- 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.
- 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
Problem
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 ```