Sheet Summary Formula Error


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

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!