Excel SUMIF between dates

Question

I have column A with date values formatted as mm/dd/yyyy. I am trying to sum the values of column B if A >=DATE(2012,1,1) AND

=SUM(B:B) sums B properly, but if I try to use =SUMIF(B:B,A:A>=DATE(2012,1,1)) the value returned is 0.00. I'm assuming this has something to do with using decimal for the sum and date type for the criteria. Is there a way to get around this?

Thanks

score 34 · Accepted Answer · answered Dec 27 '12 at 15:30

34

You haven't got your SUMIF in the correct order - it needs to be range, criteria, sum range. Try:

=SUMIF(A:A,">="&DATE(2012,1,1),B:B)

answered Dec 27 '12 at 15:30

Niall

1,551
4
23
40

Wow, I feel dumb now. Thanks. My issue with the SUMIFS was I forgot the quotes around the >= and the < for the between dates. – spassen Dec 27 '12 at 15:39
@Crazy Horse also have a look at this question..http://stackoverflow.com/q/39937769/3496570 – Zar E Ahmer Oct 09 '16 at 10:16

score 28 · Answer 2 · answered Dec 27 '12 at 15:48

28

To SUMIFS between dates, use the following:

=SUMIFS(B:B,A:A,">="&DATE(2012,1,1),A:A,"<"&DATE(2012,6,1))

answered Dec 27 '12 at 15:48

spassen

1,550
8
20
32

score 1 · Answer 3 · edited Mar 18 '14 at 12:38

I found another way to work around this issue that I thought I would share.

In my case I had a years worth of daily columns (i.e. Jan-1, Jan-2... Dec-31), and I had to extract totals for each month. I went about it this way: Sum the entire year, Subtract out the totals for the dates prior and the dates after. It looks like this for February's totals:

=SUM($P3:$NP3)-(SUMIF($P$2:$NP$2, ">2/28/2014",$P3:$NP3)+SUMIF($P$2:$NP$2, "<2/1/2014",$P3:$NP3))

Where $P$2:$NP$2 contained my date values and $P3:$NP3 was the first row of data I am totaling. So SUM($P3:$NP3) is my entire year's total and I subtract (the sum of two sumifs):

SUMIF($P$2:$NP$2, ">2/28/2014",$P3:$NP3), which totals all the months after February and SUMIF($P$2:$NP$2, "<2/1/2014",$P3:$NP3), which totals all the months before February.

score 0 · Answer 4 · answered Nov 17 '16 at 13:24

0

this works, and can be adapted for weeks or anyother frequency i.e. weekly, quarterly etc...

=SUMIFS(B12:B11652,A12:A11652,">="&DATE(YEAR(C12),MONTH(C12),1),A12:A11652,"<"&DATE(YEAR(C12),MONTH(C12)+1,1))

answered Nov 17 '16 at 13:24

RTG

1

score -1 · Answer 5 · edited Mar 15 '16 at 08:35

-1

One more solution when you want to use data from any sell ( in the key C3)

=SUMIF(Sheet6!M:M;CONCATENATE("<";TEXT(C3;"dd.mm.yyyy"));Sheet6!L:L)

edited Mar 15 '16 at 08:35

Box Box Box Box

5,094
10
49
67

answered Mar 15 '16 at 07:25

Ico

1

Excel SUMIF between dates

5 Answers5

Linked

Related