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
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!