COUNTIFS Errors

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?

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!