COUNTIFS Errors

Katherine Wilgruber

I'm trying to create a Sheet Summary field formula that will count up the number of "Yes" (formula-generated, if that matters) cells in a column if the date, in another column on that row, falls within a given month/year range.

This one seems to be working... I think:

=IFERROR((COUNTIFS(OTD:OTD, OR(@cell = "Yes", @cell = "N/A"), [BR to Customer Date]:[BR to Customer Date], AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022)) / COUNTIFS([BR to Customer Date]:[BR to Customer Date], AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022))), "N/A")

This one serves up an "N/A" 100% percent of the time:

=IFERROR((COUNTIFS(PTP:PTP, "Yes", [Actual Completion]:[Actual Completion], AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022)) / COUNTIFS([Actual Completion]:[Actual Completion], AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022))), "N/A")

I can't seem to alter them at all without getting an #INVALID OPERATION or #UNPARSEABLE in my field. Even =COUNTIF(PTP:PTP, "Yes") results in #INVALID OPERATION.

Any ideas about what I might be doing wrong?

Genevieve P.

Hi @Katherine Wilgruber

Are there instances where there are no dates in the date cells (e.g. text instead)? Try wrapping an IFERROR around that statement:

=IFERROR(IF(AND([Scheduled Start (Forecast)]@row >= [Actual Start]@row, [Scheduled Completion (Forecast)]@row >= [Actual Completion]@row), "Yes", "No"), "")

Amit Wadhwani

Hi @Katherine Wilgruber

The possible reason could be that you are using IFERROR more than 1 time and due to some reason the function is not closed properly as it should work.

MONTH(@cell), 0) = 3 the condition in this part is not correct which is creating Errors.

I would suggest use only 1 IFERROR and close all the parentheses correctly

Best Regards

Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

https://www.linkedin.com/in/amitinddr/

If my solution helped you, do not forget to mark it awesome

Katherine Wilgruber

Hi @Amit Wadhwani

Would you mind showing me an example of a corrected formula? I'm not sure how to execute your suggested solution properly.

I'm skeptical of the solution, only because even =COUNTIF(PTP:PTP, "Yes") results in #INVALID OPERATION, which I don't know how to explain.

Genevieve P.

Hi @Katherine Wilgruber

Is it possible that your column "PTP" has a formula error? Errors will create a domino effect, so if you reference an entire column in a formula but one cell in that column contains an error, your new formula will also display that error.

How are you creating "Yes" in that column?

Katherine Wilgruber

@Genevieve P. anything's possible!

My "Yes" is coming from this column formula:

=IF(AND([Scheduled Start (Forecast)]@row >= [Actual Start]@row, [Scheduled Completion (Forecast)]@row >= [Actual Completion]@row), "Yes", "No")

Genevieve P.

Hi @Katherine Wilgruber

Are there instances where there are no dates in the date cells (e.g. text instead)? Try wrapping an IFERROR around that statement:

=IFERROR(IF(AND([Scheduled Start (Forecast)]@row >= [Actual Start]@row, [Scheduled Completion (Forecast)]@row >= [Actual Completion]@row), "Yes", "No"), "")

Katherine Wilgruber

@Genevieve P. You're my HERO... that fixed it! TYSM

Genevieve P.

I'm so glad! 🙂 Thanks for letting us know.