Excel Formula to SUMIF date falls in particular month

Question

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

User · Answer

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

User · Answer

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

User · Answer

Sumifs B B A A   gt  1 1 2013  A A   lt  1 31 2013     The beauty of this formula is you can add more data to columns A and B and it will just recalculate

[excel] Excel Formula to SUMIF date falls in particular month

Examples related to excel