# Help on SUMIF function with date criteria

1. ## Help on SUMIF function with date criteria

I am trying to do a SUMIF formula where Im trying to sum the square feet of cells (sum_range column) based on the criteria_range of having the year 2017 (e.g., if there were three different cells in the criteria_range column with dates that looked like 3/1/2017, 2/1/2017 and 9/1/2017, then they would fit the criteria and I would have the function add up their respective square footages to give me my desired answer).

This is the function I have tried so far to find the sum of the square footages for cells ending in 2017: =SUMIF(G:G,"*2017",E:E)

I thought this would work because G:G is my column that I want to analyze, *2017 is my criteria with the asterisk wildcard so that it only selects dates ending in 2017, and E:E is my sum range (i.e., the column with the square footages).
However, when I plug this equation in, I get 0.00. There must be some mistake because I dont know where I am going wrong.  Register To Reply

2. ## Re: Help on SUMIF function with date criteria

Perhaps something like:
Formula:  `Please Login or Register  to view this content.`

There are other ways though.

Your formula didn't work because if the dates are stored as dates, as opposed to stored as text, then Excel doesn't see them in the same way you do.
For example Jan 1st 2017 would be 42736 in Excel's eyes. Your formula is looking for a text string ending in 2017 so you can see where Excel was getting confused.

BSB  Register To Reply

3. ## Re: Help on SUMIF function with date criteria

Got it. Yes that worked thank you. I tried a slightly different variation since I originally posted, but it achieved the same outcome as your suggestion:

=SUMIFS(\$E\$4:\$E\$19,\$G\$4:\$G\$19,">="&"1-Jan-2017",\$G\$4:\$G\$19,"<="&"31-Dec-2017")

Then I want to do the same thing where I sum the square footages for dates ending in 2018, 2019, and so on, up to 2032. When I got my answer for the sum of square footages for 2017, I did the technique of clicking on the bottom corner of the cell and dragging across to 2018, 2019, etc. but the summed value for 2017 just carried across instead of adapting to the summed square footages for 2018, 2019, etc....

Any suggestions on how to achieve this?  Register To Reply

4. ## Re: Help on SUMIF function with date criteria

Re: Help on SUMIF function with date criteria
Got it. Yes that worked thank you. I tried a slightly different variation since I originally posted, but it achieved the same outcome as your suggestion:

=SUMIFS(\$E\$4:\$E\$19,\$G\$4:\$G\$19,">="&"1-Jan-2017",\$G\$4:\$G\$19,"<="&"31-Dec-2017")

Then I want to do the same thing where I sum the square footages for dates ending in 2018, 2019, and so on, up to 2032. When I got my answer for the sum of square footages for 2017, I did the technique of clicking on the bottom corner of the cell and dragging across to 2018, 2019, etc. but the summed value for 2017 just carried across instead of adapting to the summed square footages for 2018, 2019, etc....

Any suggestions on how to achieve this?  Register To Reply

5. ## Re: Help on SUMIF function with date criteria

Probably the easiest way is to put the years in cells and reference those cells in place of the year within the DATE sections of my formula.

So if 2017 was in X1 and 2018 in Y1 and 2019 in Z1 etc.

Then for 2017 you could use:
=SUMIFS(E:E,G:G,">="&DATE(X1,1,1),G:G,"<="&DATE(X1,12,31))
As you drag that the X1 will increment to Y1 and hence the year will increment too.

Hope that makes sense. If not, post a sample workbook and we can show you how.

BSB  Register To Reply

6. ## Re: Help on SUMIF function with date criteria

Yes that also worked thank you. The final thing that I am having trouble with is that in column G (my criteria range in my initial question) there are cells that are left blank because either: the lease is a month to month (MTM) lease and therefore intentionally left blank because we don't know if the lease will be renewed, or the space is VACANT and obviously has no value (in my spreadsheet, column C shows whether the lease is occupied or VACANT).

There is one last cell that I need to fill in where I am calculating the square footages of all the MTM leases (i.e., have a blank space in column G, and in column C there is a tenant and not the word VACANT).

I have set up a similar SUMIFS formula to find this last value:

=SUMIFS(\$E:\$E,\$G:\$G,"="&0,\$C:\$C,"<>"&"VACANT")

Where E:E is my sum_range (square footages), G:G is my first criteria, and C:C is my other criteria to see whether or not there is the word VACANT.

I feel like I am close with this one but I am still not getting the correct answer. Again, I am looking for the square footages of MTM leases, therefore, even if there is no value in column G, there also must not be the word VACANT in column C in order to be a MTM lease and meet the criteria.  Register To Reply

7. ## Re: Help on SUMIF function with date criteria

The mistake is in the third argument, use this
=SUMIFS(\$E:\$E,\$G:\$G,"=",\$C:\$C,"<>"&"VACANT")  Register To Reply

8. ## Re: Help on SUMIF function with date criteria

These things are always easier with a sample workbook so we can see the layout, but perhaps the below will suit?
Formula:  `Please Login or Register  to view this content.`

BSB  Register To Reply

9. ## Re: Help on SUMIF function with date criteria

Thank you to both of you, this has really helped and I think I have it all down.

Thanks again.  Register To Reply

10. ## Re: Help on SUMIF function with date criteria

Happy to help. Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

BSB  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 