Summary Field, Date Within Range using SUMIFS

Hi, I am trying to use the following SUMIFS formula but I receive a "#INVALID OPERATION" error when trying to create a Sheet Summary Field using the formula.

=SUMIFS(Hours:Hours, Status:Status, "Confirmed", FinishDate:FinishDate, >=12/10/2021)

However, if I remove the ">=" and just use the date 12/10/2021 I do not get the invalid operation error message. That does not work because I am trying to figure out how to sum the Hours field if the project is Confirmed and the date falls after a certain date, not just on a specific date.

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    That is because you need to use the DATE function.

    DATE(yyyy, mm, dd)


    =SUMIFS(Hours:Hours, Status:Status, "Confirmed", FinishDate:FinishDate, @cell >= DATE(2021, 12, 10))

  • Paul, thank you! Figured it was something easy... is there documentation about the "@cell" piece of the formula you provided? I have not seen it before so curious of its use in this context.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    In this particular case it is not necessary. I just use it out of habit. Generally speaking the @cell reference is used to tell a function to evaluate a previously established range on a cell by cell basis as opposed to the range as a whole.


    For example, if you wanted to count how many dates in a column were in the year 2021, you would say something like this...

    =COUNTIFS([Date Column]:[Date Column], YEAR(@cell) = 2021)

    The YEAR function requires some reference within it, but we don't necessarily want to count the entire range. We want to count the individual cells within the range.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!