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!!
