Nested Criteria error only for one row

Joseph Adams

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

Genevieve P.

Hi @Joseph Adams

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

Genevieve P.

Hi @Joseph Adams

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

Joseph Adams

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

Genevieve P.

No problem! I don't know why it was that one cell either, but I'm glad it works now!