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
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!