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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @dfortuna

    =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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @dfortuna

    =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.

  • dfortuna
    dfortuna ✭✭✭✭

    Thank you so much, this fixed the issue and formula is working correctly. Appreciate your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!