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
This question is related to
excel
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, 1, 1, 2, 2}.{1, 1, 1, 2, 2}= 1 produces the array {TRUE, TRUE, TRUE, FALSE, FALSE}, which comprises the condition for the IF statement.{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.{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
=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
=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.
Source: Stackoverflow.com