locked
Sumifs Formula fails when I add a LessThan or EqualTo Date as a Criteria RRS feed

  • Question

  • I have a budget spreadsheet with three worksheets (NewBudget, NewRegister & Lists) They contain the following fields:

    NewBudget:  11/2/2017 in cell C2, 11/31/2017 in Cell D2 and a Category column with "Amazon Prime" in A4.

    NewRegister: Columns = Date, PaidTo, Description, Category, Amount, Balance

    NewRegister: Rows below

    Date/Category/Amount 

    10/15/2017, Amazon Prime, -$5.00

    11/15/2017, Amazon Prime, -$8.25

    11/16/2017, Amazon Prime, -$2.00

    12/1/2017, Amazon Prime, -$50.00

    Lists: Amazon Prime, Utilities, Mortgage A2-A4

    I have this formula insert in NewBudget!C4:

    =SUMIFS(RegAmount,RegCategory,Lists!A2,RegDate,">="&$C$2,RegDate,"<="&$D$2)

    If I remove the last criteria (,RegDate, "<="&$D$2) the formula returns -$60.25--which is incorrect because it includes the row for 12/1/2017.  However, if I leave this criteria in place the formula returns $0.00--which is also incorrect.  It is as though the SUMIFS is saying that no rows match both criteria.  But clearly 2 rows do meet the criteria.

    Can anyone assist me with getting this to work?

    Thanks.


    Walt


    • Edited by Wrdmail123 Sunday, December 3, 2017 2:06 AM
    Sunday, December 3, 2017 2:04 AM

All replies

  • Hi Wrdmail123,

    I will be happy if you share your file via cloud storage such as OneDrive, Dropbox, and so on, because it will save my time to input data or formula.
    (Remember to edit/modify your private/important data before sharing.)

    Regards,

    Ashidacchi

    Sunday, December 3, 2017 5:18 AM
  • Any help would be appreciated.

    https://drive.google.com/file/d/1vKnU2V_Z3Mbgp_F4ZTiMUn3Zn-slEs-1/view?usp=sharing

    Thanks.

    Walt

    Sunday, December 3, 2017 6:49 PM
  • November has only 30 days, so 11/31/2017 doesn't exist. As a consequence, the value 11/31/2017 in D2 is a text value, not a date value. If you change D2 to 11/30/2017, it will work as intended.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 3, 2017 8:19 PM
  • Thank you very much Hans.  I entered that date because I was just being too lazy to look at the calendar and didn't consider the consequence of how Excel treats dates (starting from 1899 or some such gibberish).

    Thanks All.

    Walt

    Sunday, December 3, 2017 9:06 PM