COUNTIFS Errors
I'm trying to create a Sheet Summary field formula that will count up the number of "Yes" (formula-generated, if that matters) cells in a column if the date, in another column on that row, falls within a given month/year range.
This one seems to be working... I think:
=IFERROR((COUNTIFS(OTD:OTD, OR(@cell = "Yes", @cell = "N/A"), [BR to Customer Date]:[BR to Customer Date], AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022)) / COUNTIFS([BR to Customer Date]:[BR to Customer Date], AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022))), "N/A")
This one serves up an "N/A" 100% percent of the time:
=IFERROR((COUNTIFS(PTP:PTP, "Yes", [Actual Completion]:[Actual Completion], AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022)) / COUNTIFS([Actual Completion]:[Actual Completion], AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022))), "N/A")
I can't seem to alter them at all without getting an #INVALID OPERATION or #UNPARSEABLE in my field. Even =COUNTIF(PTP:PTP, "Yes") results in #INVALID OPERATION.
Any ideas about what I might be doing wrong?
Best Answer
-
Are there instances where there are no dates in the date cells (e.g. text instead)? Try wrapping an IFERROR around that statement:
=IFERROR(IF(AND([Scheduled Start (Forecast)]@row >= [Actual Start]@row, [Scheduled Completion (Forecast)]@row >= [Actual Completion]@row), "Yes", "No"), "")
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
The possible reason could be that you are using IFERROR more than 1 time and due to some reason the function is not closed properly as it should work.
MONTH(@cell), 0) = 3 the condition in this part is not correct which is creating Errors.
I would suggest use only 1 IFERROR and close all the parentheses correctly
Best Regards
Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA
https://www.linkedin.com/in/amitinddr/
If my solution helped you, do not forget to mark it awesome
Best Regards
Amit Wadhwani, Smartsheet Community Champion
Smartsheet CoE, Ignatiuz, Inc., Exton, PA
Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/ -
Would you mind showing me an example of a corrected formula? I'm not sure how to execute your suggested solution properly.
I'm skeptical of the solution, only because even =COUNTIF(PTP:PTP, "Yes") results in #INVALID OPERATION, which I don't know how to explain.
-
Is it possible that your column "PTP" has a formula error? Errors will create a domino effect, so if you reference an entire column in a formula but one cell in that column contains an error, your new formula will also display that error.
How are you creating "Yes" in that column?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. anything's possible!
My "Yes" is coming from this column formula:
=IF(AND([Scheduled Start (Forecast)]@row >= [Actual Start]@row, [Scheduled Completion (Forecast)]@row >= [Actual Completion]@row), "Yes", "No")
-
Are there instances where there are no dates in the date cells (e.g. text instead)? Try wrapping an IFERROR around that statement:
=IFERROR(IF(AND([Scheduled Start (Forecast)]@row >= [Actual Start]@row, [Scheduled Completion (Forecast)]@row >= [Actual Completion]@row), "Yes", "No"), "")
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. You're my HERO... that fixed it! TYSM
-
I'm so glad! 🙂 Thanks for letting us know.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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!