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
-
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))
Answers
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!