Sheet Summary Formula Error due to Formula in Sheet

Madeline R

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?

heyjay

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

Genevieve P.

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

Madeline R

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.

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

Madeline R

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

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

Madeline R

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

KPH

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