Countifs with Column Formulas

Deb Allen

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

What is the exact column formula you are using?

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

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

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

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.

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!

Cheers,

Genevieve

Deb Allen

It did, thank you so much!