# COUNTIFS Errors

Options

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?

Tags:

Options

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"), "")

• ✭✭✭✭✭✭
Options

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

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

Best Regards

Smartsheet CoE, Ignatiuz Software, Exton, PA

• Options

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.

Options

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?

• Options

@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")

Options

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"), "")

• Options

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