COUNTIF & IFERROR Formulas

JKP0009 · 02/24/23

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.

sharkasits · 02/24/23

@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.

sharkasits · 02/24/23

@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.

JKP0009 · 03/08/23

https://community.smartsheet.com/discussion/comment/364288#Comment_364288

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!

COUNTIF & IFERROR Formulas

Best Answer

Answers

Help Article Resources

Categories