Sheet Summary Formula Error

Steve Rieger

I am trying to create summary field on a sheet to show how many rows contain a users name.

The summary formula is as follows: =COUNTIF(VP:VP, FIND("a user name", LOWER(@cell)) > 0)

This works fine when the column VP contains actual values with text. However, if I put in a column formula to calculate their name from a reference sheet, the summary field says: #NO MATCH

Here is my column formula for VP: =VLOOKUP(EntityNum@row, {PropInfoByEntityNum}, 16, false)

The column formula for VP works fine and the values are being pulled correctly. There are however, a dozen or so rows that say #NO MATCH as they legitimately do not have a value for VP. I tried filtering the sheet to remove the #NO MATCH entries in the sheet but the summary field still says #NOT MATCH.

To recap, the summary field works if I copy / paste values into the VP column even if it says #NO MATCH, but if I change the VP column to a column formula which pulls the exact same values as the copy / paste the summary field says #NO MATCH

Steve Rieger

Figured it out myself. I put some IFERROR logic in my column formula to return "N/A" instead of #NO MATCH and my summary column computes correctly now.

Steve Rieger

Figured it out myself. I put some IFERROR logic in my column formula to return "N/A" instead of #NO MATCH and my summary column computes correctly now.

Genevieve P.

Hi @Steve Rieger I'm glad you figured it out! Thanks for posting your solution. 🙂