CountIFS for smartsheet (Excel formula translation to smartsheet)
I have an excel formula that calculates the the number of days by month and status.
=COUNTIFS($E$1:$E$100,">=Aug 01 2021",$E$1:$E$100,"<=Aug 31 2021",$G$1:$G$100,"=Not Started")
When I translate this formula into smartsheet it does not work.
=COUNTIFS([End Date]1:[End Date]100,">=Aug 01 2021",[End Date]1:[End Date]100,"<=Aug 31 2021",Status1:Status100,"=Not Started")
Please not I changed the End Date column to Date which resulted in translation of MM/DD/YYY.
The formula does not return a value in the cell. Is this formula possible in smartsheet? Thank you for any assistance anyone can provide.
Best Answer
-
No. You would leave the @cell as is. It is basically telling the formula to count the previously established range on a cell by cell basis.
Answers
-
The formula in Smartsheet for August of 2021 would look like this...
=COUNTIFS($E$1:$E$100, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021), $G$1:$G$100, @cell = "Not Started")
To write it with specific dates instead of referencing a month and year number it would look more like this...
=COUNTIFS($E$1:$E$100, AND(@cell >= DATE(2021, 08, 01), @cell <= DATE(2021, 08, 31)), $G$1:$G$100, @cell = "Not Started")
-
Paul, the is helpful
For clarity the "@cell" in your string example should I just copy the column length again as I need to search the enter column for a specific month? Would it look like =COUNTIFS([End Date]1:[End Date]100, and ([End Date]1:[End Date]100>=DATE(2021,08,01),[End Date]1:[End Date]100>=DATE(2021, 08, 31)),Status 1:Status 100, Status 1:Status 100="Not Started")
Note: Smartsheet brings up the column name in formula, I need an example of @cell since this is a range.
Thanks again for your assistance.
-
Paul, the is helpful
For clarity the "@cell" in your string example should I just copy the column length again as I need to search the enter column for a specific month? Would it look like =COUNTIFS([End Date]1:[End Date]100, and ([End Date]1:[End Date]100>=DATE(2021,08,01),[End Date]1:[End Date]100>=DATE(2021, 08, 31)),Status 1:Status 100, Status 1:Status 100="Not Started")
Note: Smartsheet brings up the column name in formula, I need an example of @cell since this is a range.
Thanks again for your assistance.
-
No. You would leave the @cell as is. It is basically telling the formula to count the previously established range on a cell by cell basis.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!