sum between dates

sum the amount if the dates range is from Jan 1st - Jan 31st.. Any idea?

Best Answer

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Samuel Dowdy Jr

    SUMIFS is the function you need. It has the syntax (range to be summed, range1, criteria1, range2, criteria2, range3, criteria3) and you can have as many or as few criteria as you need, making sure they are always entered in a range-criteria pair. Be sure that that sum-range is a range that contains sumable values.

    One way of looking at your criteria is to search for the entire month

    =SUMIFS([your sum column]:[your sum column], [your date]:[your date], IFERROR(MONTH(@cell), 0) = 1)

    Date functions are notorious for having errors if cells are blank, or text in the field so it not uncommon to see the IFERROR around a date function

    To look at SUMIFS between two dates you need to use the DATE function

    =SUMIFS([your sum column]:[your sum column], [your date]:[your date], AND(@cell>=DATE(2021,1,1), @cell<=DATE(2021, 1, 31))

    These formulas assume you are looking at data within the same sheet as your formula. You will need cross-sheet references if the data is in a different sheet

    Here's more info on the formulas I used.

    cheers,

    Kelly

  • Samuel Dowdy Jr.
    Samuel Dowdy Jr. ✭✭✭✭✭✭

    =SUMIFS({MASTER - Invoice Log Sheet Range 1}, {MASTER - Invoice Log Sheet Range 5}, Data4, {MASTER - Invoice Log Sheet Range 4}, AND(>=DATE(2021, 1, 1), <=DATE(2021, 1, 31)))


    This what I have that's not working

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Samuel Dowdy Jr

    Try this

    =SUMIFS({MASTER - Invoice Log Sheet Range 1}, {MASTER - Invoice Log Sheet Range 5}, Data@row, {MASTER - Invoice Log Sheet Range 4}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!