Formula Error - Counting Prior Month Numbers from Different Departments
Hi...
I'm trying to capture information from 2 different references and the formula below is giving me an #incorrect argument. The formula will work if I remove the second reference which is giving me the total for items created prior month for the entire sheet. However, I need to break down the totals by different categories that make up the entire total.
=IF(MONTH(TODAY()) = 1, COUNTIFS({Date Completed}, IFERROR(MONTH(@cell), 0) = 12, {Date Completed}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Date Completed}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Date Completed}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), {MSP1}, "Internal IT")
This is a repost - I had the incorrect category.
Thank you!
Best Answer
-
=IF(MONTH(TODAY()) = 1, COUNTIFS({Date Completed}, IFERROR(MONTH(@cell), 0) = 12, {Date Completed}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Date Completed}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Date Completed}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {MSP1}, "Internal IT"))
I think this is what you're after, maybe. Right before the {MSP1} it looks like you have an extra parenthesis which was mistakenly closing off the formula. So anything after that was causing a problem.
Answers
-
=IF(MONTH(TODAY()) = 1, COUNTIFS({Date Completed}, IFERROR(MONTH(@cell), 0) = 12, {Date Completed}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Date Completed}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {Date Completed}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {MSP1}, "Internal IT"))
I think this is what you're after, maybe. Right before the {MSP1} it looks like you have an extra parenthesis which was mistakenly closing off the formula. So anything after that was causing a problem.
-
Thank you so much, this fixed the issue and formula is working correctly. Appreciate your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!