COUNTIF & IFERROR Formulas
Hey all!
I'm getting a data discrepancy when I'm trying to count all of the In Progress requests that we've received in a year. The formula that I'm using is this: =COUNTIFS({Created}, IFERROR(YEAR(@cell), 0) = 2022, {Crew Support Status}, "In Progress")
The Created Column uses a formula that pulls the YEAR from the Created Date column in a sheet. My formula above generates the value of 115, but when I filter the sheet to show the same thing, I get a value of 117. I can't figure out what I'm doing wrong here, so any additional help would be greatly appreciated.
Best Answer
-
@JKP0009 It's hard to tell without a sample of the data, but what I would do to trouble shoot is add a test checkbox column and put this formula in.
=IF(AND(IFERROR(YEAR([Created]@row), 0) = 2022, [Crew Support Status] = "In Progress"),1,0)
Then filter how you were and add a filter for where the test column is unchecked. That should help identify which rows aren't getting counted.
Answers
-
@JKP0009 It's hard to tell without a sample of the data, but what I would do to trouble shoot is add a test checkbox column and put this formula in.
=IF(AND(IFERROR(YEAR([Created]@row), 0) = 2022, [Crew Support Status] = "In Progress"),1,0)
Then filter how you were and add a filter for where the test column is unchecked. That should help identify which rows aren't getting counted.
-
So I figured out what was wrong with my data! There were two errant manual entries when I filtered that was being included. But your solution helped me figure that out! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!