COUNTIF using IFERROR and MONTHs

Amy Evans

Hello,

I'm trying to count a range of cells under the Due Date column in a referenced sheet when the dates have months of October, November or December.

I saw another post about using IFERROR when experiencing Invalid Data Type which is what I experienced. The formula worked when I only counted for October (Month=10), but when I added November (11) and December (12), I receive UNPARSEABLE error.

=COUNTIF({Due date}, IFERROR(OR((MONTH(@cell), 0) = 10, (MONTH(@cell), 0) = 11, (MONTH(@cell), 0) = 12)))

Thanks,

Amy

Nic Larsen

Try this:

=COUNTIFS({Due date}, IFERROR(OR(MONTH(@cell) = 11, MONTH(@cell) = 10, MONTH(@cell) = 12), 0))

Amy Evans

I'm receiving an INVALID DATA TYPE error.

Nic Larsen

Try this one instead:

=COUNTIF({Due date}, OR(IFERROR(MONTH(@cell), 0) = 10, IFERROR(MONTH(@cell), 0) = 11, IFERROR(MONTH(@cell), 0) = 12))

Amy Evans

Yes, that worked!

A few follow-up questions so I know what to do next time:

1) Why is the IFERROR after the "OR" function?

2) What does zero mean in this segment, "MONTH(@cell), 0"?

3) I noticed we switched back to "COUNTIF" instead of "COUNTIFs". Can you explain why we switched?

Thank you so much. The answers to my questions will help me fully grasp the solution better.

Amy

Nic Larsen

You bet. Let me see.

1) You first picked your range and then you need the criteria. In this case you have multiple criteria so you start with OR, followed by the IFERROR in case there was a data error at the cell level.

2) So if it encounters an error with the data in the cell or it finds a blank cell, it counts it as zero. Otherwise, it will stop counting and just give you an error code.

3) Countif/ifs - probably just a typo on my part. This one was giving me a headache at first and was trying different options. You can just add the S back in. I normally always do Countifs, even if I only have 1 set of range/criteria.

David Joyeuse

Hi @Amy Evans

To answer your questions:

1. The IFERROR is here in case the MONTH() function returns an error. The OR is first because you're looking for 3 differents month.
2. The 0 is the second part of the IFERROR function. The function returns the month of the cell or a 0 if the cell is not a date.
3. That one doesn't matter much. @Nic Larsen switched to COUNTIF because there's only one range, and one criteria (with 3 options though). So COUNTIF works nicely here. COUNTIFS would work likewise.

As a whole, it's better to use COUNTIFS and SUMIFS instead of COUNTIF and SUMIF, even if there's just one criteria for both.

Amy Evans

Thank you, Nic and David! I really appreciate the follow-up explanations.

Amy