+ Reply to Thread
Results 1 to 10 of 10

Help on SUMIF function with date criteria

  1. #1
    Registered User
    Join Date
    06-20-2016
    Location
    Wisconsin, USA
    MS-Off Ver
    365 ProPlus
    Posts
    41

    Help on SUMIF function with date criteria

    I am trying to do a SUMIF formula where Im 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 dont know where I am going wrong.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,635

    Re: Help on SUMIF function with date criteria

    Perhaps something like:
    Formula: copy to clipboard
    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
    Last edited by BadlySpelledBuoy; 06-21-2016 at 02:53 PM.

  3. #3
    Registered User
    Join Date
    06-20-2016
    Location
    Wisconsin, USA
    MS-Off Ver
    365 ProPlus
    Posts
    41

    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?

  4. #4
    Registered User
    Join Date
    06-20-2016
    Location
    Wisconsin, USA
    MS-Off Ver
    365 ProPlus
    Posts
    41

    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?

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,635

    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

  6. #6
    Registered User
    Join Date
    06-20-2016
    Location
    Wisconsin, USA
    MS-Off Ver
    365 ProPlus
    Posts
    41

    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.

  7. #7
    Registered User
    Join Date
    06-16-2016
    Location
    Greece
    MS-Off Ver
    2013
    Posts
    69

    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")
    instead, otherwise you'll get zero

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,635

    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: copy to clipboard
    Please Login or Register  to view this content.


    BSB
    Last edited by BadlySpelledBuoy; 06-21-2016 at 04:12 PM. Reason: EDIT: Appears Nick93 beat me to it.

  9. #9
    Registered User
    Join Date
    06-20-2016
    Location
    Wisconsin, USA
    MS-Off Ver
    365 ProPlus
    Posts
    41

    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.

  10. #10
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,635

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUMIF(S) with date criteria
    By jfs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-30-2014, 01:20 PM
  2. [SOLVED] Help with Sumif function please, need two criteria
    By JCarollo765 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-05-2014, 12:35 PM
  3. [SOLVED] SUMIF function for 2 criteria
    By stonecul in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-08-2014, 08:24 PM
  4. [SOLVED] SUMIF within date criteria?
    By loshington in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-24-2013, 10:45 AM
  5. SUMIF criteria using the AND function
    By BobTheRocker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2011, 11:34 AM
  6. SumIf using date criteria range and one other criteria
    By DJTMONEY in forum Excel General
    Replies: 1
    Last Post: 04-20-2010, 04:10 PM
  7. Two Criteria in SumIf Function
    By Jack Gillis in forum Excel General
    Replies: 3
    Last Post: 11-15-2005, 01:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1