#nested criteria multiple columns

Michelle Maas

I have a timesheet that I'm using formulas to calculate the different categories of time using three columns - Date, OOH (1.5x) and OOH (2x). The first formula below is the original:

=SUMIFS([OOH (1.5x)]:[OOH (1.5x)], Date:Date, AND(IFERROR(MONTH(@cell), 0) = 8)) + SUMIFS([OOH (2x)]:[OOH (2x)], Date:Date, AND(IFERROR(MONTH(@cell), 0) = 8))

I have now changed it to automatically update using the below formula which works in two timesheets but when I try it in another timesheet it brings up #nested criteria. The timesheets are identical so there is no reason why it should not work. What am I missing?

=SUMIFS([OOH (1.5x)]:[OOH (1.5x)], Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) + SUMIFS([OOH (2x)]:[OOH (2x)], Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))))

Paul Newcome

It looks like there are just some parenthesis issues. Technically you have the second SUMIFS nested inside of the first. If you looks just before the plus symbol, you will see that you close out the TODAY function then the YEAR function then the AND function, but you don't close out the first SUMIFS before moving on to the second.

TODAY()

YEAR(TODAY())

AND(........YEAR(TODAY()))

SUMIFS(.......AND(.......YEAR(TODAY())))

Try moving one closing parenthesis from the very end of the formula to there before the plus. Each SUMIFS (assuming you are using the same syntax but with different sheet references) should end with 4 closing parenthesis.

Paul Newcome

It looks like there are just some parenthesis issues. Technically you have the second SUMIFS nested inside of the first. If you looks just before the plus symbol, you will see that you close out the TODAY function then the YEAR function then the AND function, but you don't close out the first SUMIFS before moving on to the second.

TODAY()

YEAR(TODAY())

AND(........YEAR(TODAY()))

SUMIFS(.......AND(.......YEAR(TODAY())))

Try moving one closing parenthesis from the very end of the formula to there before the plus. Each SUMIFS (assuming you are using the same syntax but with different sheet references) should end with 4 closing parenthesis.

Michelle Maas

Fantastic, such a simple fix but good to know. Thank you for your help, @Paul Newcome. Have a great weekend.

Paul Newcome

Happy to help. 👍️