Nested Criteria error only for one row
Hello Community,
I have a column formula that works fine for every row except one. This particular row is not any different that I can see from the others. I apologize for the length of the formula, I will do my best to isolate and breakdown the issue I am having:
Here is a pic of a test column created to work out the proper column formula:
The formula itself is as follows, again sorry for the length. The bolded text is just the rolling quarter formula. I am basically getting a usage total for some material we use.
=SUMIFS([Active Polished]:[Active Polished], Created:Created, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), Name:Name, [Report Name]@row) + SUMIFS({QC Amount - Active}, {Nuc Name - Active}, Name@row, {QC Date - Active}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), {Active- EA Use Case}, 0) + SUMIFS({Active Amount - Transfer}, {Nuc Name - Transfer}, Name@row, {Created Date - Transfer}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) - SUMIFS([Active Polished]:[Active Polished], Created:Created, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), Name:Name, [Report Name]@row) - IF(CONTAINS("Chase", [Report Name]@row), COUNTIFS({EA Helper - Chase Mix}, 0, {Created Date - Chase Mix}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1))) * 15, 0))
If I remove the subtracted totals from the above formula then the Nested Criteria error goes away so I am assuming it has something then to do with 2nd half of the formula:
(...) - SUMIFS([Active Polished]:[Active Polished], Created:Created, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), Name:Name, [Report Name]@row) - IF(CONTAINS("Chase", [Report Name]@row), COUNTIFS({EA Helper - Chase Mix}, 0, {Created Date - Chase Mix}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1))) * 15, 0))
Furthermore if I remove the very last line shown below than the Nested Criteria persists :
IF(CONTAINS("Chase", [Report Name]@row), COUNTIFS({EA Helper - Chase Mix}, 0, {Created Date - Chase Mix}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1))) * 15, 0))
This leads me to believe that the error lies in the 2nd to last portion of the formula:
- SUMIFS([Active Polished]:[Active Polished], Created:Created, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), Name:Name, [Report Name]@row)
If I pull this formula into a separate cell, it works. Same goes for the very last line of formula; works fine when used in isolation. If I take both of these last line together into a new cell than it also works and returns a value with no errors. It is only when these two are both used in the original formula do I get an error, and only in 1 row. Any help would be appreciated!!
Best Answer
-
You're right, the formula structure for that end piece is correct (I just tested) so you shouldn't receive an error... however it looks like you might be missing a closing parentheses right before that end -SUMIFS.
If I'm understanding you correctly, this should be your formula structure:
=SUMIFS(formula) + SUMIFS(Formula) + SUMIFS(Formula) - SUMIFS(Formula) - IF(CONTAINS(formula), COUNTIFS(formula) * 15, 0)
However this is your current structure:
=SUMIFS(formula) + SUMIFS(formula) + SUMIFS(formula - SUMIFS(formula) - IF(CONTAINS(formula), COUNTIFS(formula) * 15, 0))
So in your formula:
+ SUMIFS({Active Amount - Transfer}, {Nuc Name - Transfer}, Name@row, {Created Date - Transfer}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) - SUMIFS([Active Polished]:[Active Polished],
Should be:
MONTH(TODAY()), 1))) - SUMIFS([Active Polished]:[Active Polished],
This will close off the previous SUMIFS so you can then subtract the next section. Don't forget to take off the extra closing parentheses at the end of the formula, as well!
Let me know if you're still seeing an error after this... you may want to drag-fill the formula up one cell then down again to refresh it.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
You're right, the formula structure for that end piece is correct (I just tested) so you shouldn't receive an error... however it looks like you might be missing a closing parentheses right before that end -SUMIFS.
If I'm understanding you correctly, this should be your formula structure:
=SUMIFS(formula) + SUMIFS(Formula) + SUMIFS(Formula) - SUMIFS(Formula) - IF(CONTAINS(formula), COUNTIFS(formula) * 15, 0)
However this is your current structure:
=SUMIFS(formula) + SUMIFS(formula) + SUMIFS(formula - SUMIFS(formula) - IF(CONTAINS(formula), COUNTIFS(formula) * 15, 0))
So in your formula:
+ SUMIFS({Active Amount - Transfer}, {Nuc Name - Transfer}, Name@row, {Created Date - Transfer}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) - SUMIFS([Active Polished]:[Active Polished],
Should be:
MONTH(TODAY()), 1))) - SUMIFS([Active Polished]:[Active Polished],
This will close off the previous SUMIFS so you can then subtract the next section. Don't forget to take off the extra closing parentheses at the end of the formula, as well!
Let me know if you're still seeing an error after this... you may want to drag-fill the formula up one cell then down again to refresh it.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Genevieve you are wonderful! That fixed it. However I still dont understand why it only gave an error in that one row and not all of the rows with the formula.
Thanks so much for taking the time to look through the long formula
-
No problem! I don't know why it was that one cell either, but I'm glad it works now!
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.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!