SUMIFS with date range as criteria

[stevenxgodoy]

I have exported some data from our accounting system and I'm trying to come up with a formula that will sum column D only for the rows that fall within a specific date range (ex. 10/1/2011-10/31/2011 or 11/1/2011-11/30/2011). The other criteria that I require is that the sum is for each unique instance of column A + column B.

I have tried the following formula, as well as several variations of it, but I have not had any luck with anything that I've tried. I have even created a concatenated value for column A+B (ex. SMITH,JOHN07313) to see if that was the problem, but still no luck.

=SUMIFS(D:D,A:A,A3,B:B,B3,E:E,E3>10/1/2011,E:E,E3<10/31/2011)

I would like to have this formula in several different columns for each project (column B) that we have... each having a different budget period.

If I am not explaining my problem with enough detail please let me know and I will try to expand on it.

Thank you in advance!

*This is just a portion of the data (with names changed for privacy reasons)

<TABLE style="WIDTH: 350pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=468><COLGROUP><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" width=128><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" span=2 width=67><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 96pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=128>Column A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=67>Column B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=67>Column C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=131>Column D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=75>Column E</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>SMITH,JOHN</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>07313</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>1-5221</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>($11.11)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>10/02/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>SMITH,JOHN</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>07313</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>1-5221</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>$18.51 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>10/14/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>SMITH,JOHN</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>07313</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>1-5220</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>($28.66)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>11/15/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>DOE,JANE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>07500</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>1-5220</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>$47.78 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>10/07/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>DOE,JANE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>07500</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>1-5210</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>($8.27)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>10/22/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>DOE,JANE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>07500</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>1-5210</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>$13.78 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>11/11/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>DOE,JANE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>07500</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>1-5112</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>($111.06)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>11/15/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>DOE,JANE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>07500</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>1-5112</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>$185.10 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>12/15/2011</TD></TR></TBODY></TABLE>

 

[AlanDester]

I am having trouble with excel 2013. I am using Sumifs and the final criteria has to be a date range. When I add this criteria it just produces an error message saying the date range was not acceptable. The formula that I am using is

Range,">01/01/2016",RANGE,"<30/04/16"

Can you see what is wrong with this?

Thx

 

[Ron Coderre]

Untested....
I think you need to have Excel convert the "date" to a date.
Try something like this:

Range,">"&--"01/01/2016",RANGE,"<"&--"30/04/2016"

Make sure that the date text you enter can be understood by your region settings.
Example, for my region settings (USA)

Col_E         Col_F   Results
30/04/16      =--E2   #value
30-Apr-16     =--E3   04/30/2016
04/30/16      =--E4   04/30/2016

Does that help?

 

[AlanDester]

Untested....
I think you need to have Excel convert the "date" to a date.
Try something like this:

Range,">"&--"01/01/2016",RANGE,"<"&--"30/04/2016"

Make sure that the date text you enter can be understood by your region settings.
Example, for my region settings (USA)

Col_E         Col_F   Results
30/04/16      =--E2   #value
30-Apr-16     =--E3   04/30/2016
04/30/16      =--E4   04/30/2016

Does that help?

Thanks for that.

I have tried that and it is still giving me a "value" error. All my other criteria for the sumifs are fine, just this one. The field is formatted as a short date field. I have tried formatting it as a long date and it makes no difference.

 

[Ron Coderre]

Fomatting has no impact on whether Excel can understand the date or not.

Test by entering these in empty cells and see if Excel interprets them as dates:
--"30/04/16"
--"01/01/2016"

I'm guessing that the Jan 1 date is not an issue because the date is valid for m/d/y regions and d/m/y regions.

 

[AlanDester]

Fomatting has no impact on whether Excel can understand the date or not.

Test by entering these in empty cells and see if Excel interprets them as dates:
--"30/04/16"
--"01/01/2016"

I'm guessing that the Jan 1 date is not an issue because the date is valid for m/d/y regions and d/m/y regions.

Yes, when entered in the empty cells both are recognised and converted to 01/01/2016 and 30/04/2016

 

[AlanDester]

The code causing the problem now is:

'[O365 Connection Tracker - Q2 2016.xlsx]Indirect'!$AD$3:$AD$69,">="&--"01/01/16",'[O365 Connection Tracker - Q2 2016.xlsx]Indirect'!$AD$3:$AD$69,"<="&--"31/05/16"

 

[Aladin Akyurek]

The code causing the problem now is:

'[O365 Connection Tracker - Q2 2016.xlsx]Indirect'!$AD$3:$AD$69,">="&--"01/01/16",'[O365 Connection Tracker - Q2 2016.xlsx]Indirect'!$AD$3:$AD$69,"<="&--"31/05/16"

O365 Connection Tracker - Q2 2016.xlsx must be open for SumIfs to work.

Otherwise, try to switch to an array-processing formula like:

=SUMPRODUCT(SumRange,--('[O365 Connection Tracker - Q2 2016.xlsx]Indirect'!$AD$3:$AD$69>="01/01/16"+0),
--('[O365 Connection Tracker - Q2 2016.xlsx]Indirect'!$AD$3:$AD$69<="31/05/16"+0))

 

[samyscraps01]

Ron,

I am an Excel newbie but your formula below worked perfectly for me! However, I was wondering if you had any insight into modifying your formula to account for a thing or two. Sorry about my table, it's very plain. This is my working formula and it works perfectly, except for one hitch. First let me explain the logistics.

1. B1 and C1 are both drop down menus with dates based off a report. I can change the date range at any point by clicking on the drop down menu.

2. A3 is also a drop down menu, if you look at A2 it says accounts, that is only a title for cell A3. In A3 there is a long list of various account numbers I can choose from.

3. Therefore, based on the formula you created below, if I change the account number and the date ranges my total expenses shown on B3 will change. In cell B3 you will see a dash and that's because I have selected All in the A3 cell. My formula doesn't account for selecting All therefore it results in a dash.

4. I need my formula below to give me a result when I select All. It works great if I have an account number selected but when I change to All it results in a dash. I don't know why and the formula otherwise works great. I don't want to change it. I just want to incorporate the All into it. How can I do this?

=SUMIFS('Scratch Pads'!F:F,'Scratch Pads'!B:B,Summary!$A$3,'Scratch Pads'!H:H,">="&Summary!B1,'Scratch Pads'!H:H,"<="&Summary!C1)
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date Range[/TD]
[TD]1/1/2016[/TD]
[TD]12/22/2016[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Accts[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]All[/TD]
[TD]-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

First, it seems, based on your posted formula, that you might want to sum Col_D for rows
where:
Col_A matches cell A3
Col_B matches cell B3
and
Col_E is between 2011-10-01 and 2011-10-31, inclusive.

If that's true, try this regular formula:

=SUMIFS(D:D,A:A,A3,B:B,B3,E:E,">=10/1/2011",E:E,"<=10/31/2011")

With your sample data, it returns: 7.4

Regarding your other request, I probably need more information, but I think a pivot table would be your easiest approach.

Does that help?

 

[Aladin Akyurek]

@samyscraps01

Does 'Scratch Pads'!B:B consist of text values?

 

[samyscraps01]

Hi, I forgot to mention that Scratch Pads is the tab where it is pulling the information from a report. Summary tab is where I am displaying the results. B:B consists of a list of account numbers, H:H consists of dates, and F:F is just amounts. Everything is numbers, no words text. As an FYI, cell A3 on the dropdown, says All and it's the only text. Of course that is being referenced from a data validation list being pulled from another tab, that list includes a blank, All, and account numbers. The all is not present on the Scratch Pads column B:B.