Summary Field, Date Within Range using SUMIFS

Aaron A

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.

Paul Newcome

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 Newcome

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))

Aaron A

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

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.