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.

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?

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?

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

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.

The mistake is in the third argument, use this
=SUMIFS($E:$E,$G:$G,"=",$C:$C,"<>"&"VACANT")
instead, otherwise you'll get zero

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

Thanks again.

Happy to help.

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

BSB