Is there a way to combine a sumif and a countif function within one formula?
I am trying to find the number of "tasks" completed within a certain date range.
The following are the formulas I have been using, but I can't figure out how to combine them.
=SUMIF({Content Type}, CONTAINS("Wiki", @cell), {Task Counter})
=COUNTIF({Date Received}, >=DATE(2022, 1, 1))
Answers
-
Can you provide a little more info on your use case here?
It seems like you want to just use SUMIFS, which allows you to specify more than one criteria range. The syntax is:
SUMIFS(range to sum, criterion range 1, criterion 1, criterion range 2, criterion 2...)
So in your case:
=SUMIFS({Task Counter}, {Content Type}, CONTAINS("Wiki", @cell), {Date Received}, >=DATE(2022, 1, 1))
English: Add the values from task counter column from rows where content type contains wiki and the date received is on or after January 1, 2022.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
What I want to accomplish is to filter out the Content Type with the word wiki that was received on or after January 1, 2022 and count up the task counter for those rows.
I tried using the formula you suggested as the description is what I am trying to accomplish, but it still isn't working.
-
Are you getting an error message? That error message can tell you what might be wrong.
Formula Error Messages | Smartsheet Learning Center
I suspect it's either a range issue or a data issue. Make sure your Date Received column is really a date-type column containing real date values. You should see the little calendar icon when you click in one of the cells. Make sure your range sizes match (either the entire column or the exact same sub-set of rows.)
The formula itself works:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!