Help with multiple IFERROR statements
Hi team - I have a sheet that has a "created" date that is auto generated. Now that we're into 2021 I need to differentiate 2021 vs 2020 months.
I used the following formula last year to get a count of something specific:
=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, IFERROR(MONTH(@cell), 0) = 1)
But now that we're into 2021, how do I specify the same formula statement but include months with 2021 included?
Thanks,
Jeff
Best Answer
-
Hi Jeff,
You got me curious enough to set up a test on my side. The formula works for me. I suspect you have an issue with one of your data ranges. I'd rebuild the formula and insert new ranges to confirm they are correct.
This is what worked for me. I can change the month, year or checkboxes and get the correct responses:
=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@Cell), 0)=2021))
{(Source Data) Main row} and {(Source Data) Exempt} need to be formatted as Checkbox columns. {(Source Data) Created} is a system generated Created column.
Good luck.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi Jeff,
Try:
=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, AND(IFERROR(MONTH(@cell), 0) = 1, YEAR(@Cell)=2021))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk - I get an UNPARSEABLE error when I copy/paste that in
-
Hi Jeff,
Try this much and see if it works. =COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0)
If it does, the issue is in: {(Source Data) Created}, AND(IFERROR(MONTH(@cell), 0) = 1, YEAR(@Cell)=2021)
Let me know what you find.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi @Mark Cronk - that portion worked by itself. Also, not sure if I was clear initially, but the full formula I used last year worked - all I had to do was change the MONTH number:
=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, IFERROR(MONTH(@cell), 0) = 1)
So definitely a problem incorporating the AND(IFERROR(MONTH(@cell), 0) = 1, YEAR(@Cell)=2021) recommendation you provided.
Should that be written another way?
-
Good morning,
The syntax in the formula I provided looks right but let's try this instead:
=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, IFERROR(MONTH(@cell), 0) = 1, {(Source Data) Created}, IFERROR(YEAR(@Cell), 0)=2021)
To get 2021 results you need to insert the criteria YEAR(@cell)=2021, where the cell is the date created on your external reference.
Work this time?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hmm still unparseable
-
Very strange. The formula looks right. Is your {(Source Data) Created} range formatted as a date column? Confirm all of the range names are correct in the formula.
Maybe someone else can spot the error or has an idea about the cause.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi @Mark Cronk - it is definitely strange behavior. Yes, the (Source Data) Created) column is a system generated date column:
Here are some of the "source data" fields, to include the Created column:
-
Hi Jeff,
You got me curious enough to set up a test on my side. The formula works for me. I suspect you have an issue with one of your data ranges. I'd rebuild the formula and insert new ranges to confirm they are correct.
This is what worked for me. I can change the month, year or checkboxes and get the correct responses:
=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@Cell), 0)=2021))
{(Source Data) Main row} and {(Source Data) Exempt} need to be formatted as Checkbox columns. {(Source Data) Created} is a system generated Created column.
Good luck.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
That last one worked, @Mark Cronk ! I had to manually input the formula step by step - it didn't work when I copy/pasted.
Thanks for sticking this out with me! 👍️
-
Perfect. Glad you found a solution. Thanks for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!