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
-
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.
Answers
-
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.
-
Hi @Steve Rieger I'm glad you figured it out! Thanks for posting your solution. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!