Countifs with Column Formulas

Deb Allen
Deb Allen ✭✭✭
edited 12/17/20 in Formulas and Functions

I need a formula that will count if a course is completed and the month, for example

=COUNTIFS(Month:Month, ="October", Status:Status, ="completed")

This is returning the correct information.

But, I am using a column formula for the Month and the COUNTIFS formula only works if the column formula is removed. Once I convert the Month column back to a column formula the above formula no longer works. I tried adding an IFERROR, but that didn't work either. Any suggestions?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What is the exact column formula you are using?

  • Deb Allen
    Deb Allen ✭✭✭

    Hi, Paul

    Here is the column formula I'm using.

    =IF(MONTH([Start Date]@row) = 1, "January", IF(MONTH([Start Date]@row) = 2, "February", IF(MONTH([Start Date]@row) = 3, "March", IF(MONTH([Start Date]@row) = 4, "April", IF(MONTH([Start Date]@row) = 5, "May", IF(MONTH([Start Date]@row) = 6, "June", IF(MONTH([Start Date]@row) = 7, "July", IF(MONTH([Start Date]@row) = 8, "August", IF(MONTH([Start Date]@row) = 9, "September", IF(MONTH([Start Date]@row) = 10, "October", IF(MONTH([Start Date]@row) = 11, "November", IF(MONTH([Start Date]@row) = 12, "December"))))))))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Hmm... You may want to reach out to support. I have tested using a COUNTIFS on a column containing formulas and then converting that formula into a column formula, and it continued to work just fine for me.

  • Deb Allen
    Deb Allen ✭✭✭
    edited 12/17/20

    Ok, thank you for getting back to me! I will reach out to support.

    I should have noted before, not every row has data, so I don't know if that is where the issue is. It only is an issue with the Month column when I convert to the Column formula.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That shouldn't be an issue. It may just be that the sheet needs refreshed on the back-end (which support can do).

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Deb Allen

    I touched base with our Support team about this and they have let me know they just sent you an email with a possible solution.

    Since you are using the MONTH function and you noted that this doesn't work when your rows don't have data, it's likely that you just need to wrap an IFERROR statement around each MONTH function.

    The emailed response has an example of how to do this:

    =IF(IFERROR(MONTH([Start Date]@row), 0) = 1, "January"... etc.

    Please do let us know if this has resolved your issue!



  • Deb Allen
    Deb Allen ✭✭✭

    It did, thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!