#nested criteria multiple columns
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()))))
Best Answer
-
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.
Answers
-
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.
-
Fantastic, such a simple fix but good to know. Thank you for your help, @Paul Newcome. Have a great weekend.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!