Sheet Summary Formula Error due to Formula in Sheet

Madeline R
Madeline R ✭✭
edited 03/07/24 in Formulas and Functions

I'm using Sheet Summaries to total up information in a large sheet. This sheet also uses formulas to pull information from other sheets. I receive a #NO MATCH error when attempting to use a COUNTIF formula in the Sheet Summary on fields that are being pulled from other sheets via formulas. Is there any way around this issue?

Answers

  • heyjay
    heyjay ✭✭✭✭✭

    If you can provide your formula, we can review and provide suggestions.

    ...

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @Madeline R

    Formula errors create a sort of domino effect. If you have an error in even just one cell, then you reference the entire column in a different formula, the single-cell error will appear in your new formula.

    Try wrapping all of your formulas in the grid of the sheet with an IFERROR statement, like so:

    =IFERROR(formula, "")

    Then your sheet summary formula will work as expected!

    Cheers,

    Genevieve

  • I don't think it's the formula itself that's the issue. I'm trying to pull information from the sheet that is already using a formula to pull info into the sheet itself.

    This is the formula in the Sheet Summary: =COUNTIFS(Region:Region, "AM", Stage:Stage, "Live") and the Region column it's pulling from has 'AM' throughout, but that is being pulled into the sheet by a formula referencing another sheet

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Madeline R

    Yes exactly! Your COUNTIFS formula is just fine - you'll be seeing NO MATCH because of an error in a referenced column, likely the Region column.

    Can you scroll down through that column to see if even on cell says #NO MATCH? If there's an error in the referenced column it will bubble up into the COUNTIFS that's looking into the column.

    You'd want to add the IFERROR to the formula in the Region column, not the countifs. Does that make sense?

    Cheers,

    Genevieve

  • Hi @Genevieve P.!

    Thank you for that. I did check and with the filter of 'Live' all fields have information showing - no #NO MATCH error. Without the "Live" there are a couple #NO MATCH - but that shouldn't affect this formula, should it?

  • KPH
    KPH ✭✭✭✭✭✭

    The #NO MATCH in the data sheet will cause an error if it is in a column you are using. It does not matter if the error is on a row that meets your criteria, or not.

    If you wrap the formula in the column on the datasheet in IFERROR you can change the error from #NO MATCH to "". This will then enable the COUNTIFS to work.

    =IFERROR(your original formula,"")

  • Just want to say THANK YOU! The IFERROR solved my problem - and in more places than just here. Thank you!

  • KPH
    KPH ✭✭✭✭✭✭

    Wonderful! It's a handy little function, isn't it!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!