SUMIFS - between Dates

Options

I am trying to find the total value of products sold with a particular ID in a particular month. I used the formula below to find the value of items with the code reference LSCl30 sold in July 2020. My settings are such that first of July is written 1/7/2020. The result returned is UNPARSEABLE. I tried using 07 but that did not help. Any assistance will be greatly appreciated


=SUMIFS(Net:Net, [Item Code]:[Item Code], "LSCI30",[Invoice Date]:[Invoice Date],<2020, 31, 7), [Invoice Date]:[Invoice Date],>=2020, 1 ,7)

Best Answer

  • Kevin St-Pierre
    Kevin St-Pierre ✭✭✭
    edited 12/11/20 Answer ✓
    Options

    It seems like your formula has , (comma) instead of / to separate Year/Month/Day..

    I would give that a try first:

    =SUMIFS(Net:Net, [Item Code]:[Item Code], "LSCI30",[Invoice Date]:[Invoice Date],=<31/7/2020, [Invoice Date]:[Invoice Date],>=1/7/2020)


    If not, that should also work.. :

    =SUMIFS(Net:Net, [Item Code]:[Item Code], "LSCI30",[Invoice Date]:[Invoice Date],=<DATE(2020,07,31), [Invoice Date]:[Invoice Date],>=DATE(2020,07,01))

Answers

  • Kevin St-Pierre
    Kevin St-Pierre ✭✭✭
    edited 12/11/20 Answer ✓
    Options

    It seems like your formula has , (comma) instead of / to separate Year/Month/Day..

    I would give that a try first:

    =SUMIFS(Net:Net, [Item Code]:[Item Code], "LSCI30",[Invoice Date]:[Invoice Date],=<31/7/2020, [Invoice Date]:[Invoice Date],>=1/7/2020)


    If not, that should also work.. :

    =SUMIFS(Net:Net, [Item Code]:[Item Code], "LSCI30",[Invoice Date]:[Invoice Date],=<DATE(2020,07,31), [Invoice Date]:[Invoice Date],>=DATE(2020,07,01))

  • Philip Arnfield
    Options

    Thank you Kevin, and please accept my apology for the very late acknowledgement.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!