Counting the results of and Index match not functioning...
Hello,
Has any ever run into an issue where you are using an index match or VLookup to retrieve text, lets say the category for a row and then you want to count how many rows are assigned to that category. Index(Match) formula is working great. But when I build the Countif in the summary field I get #No Match for all of them when I can plainly see them in the column. I even copied the result of the column into the formula to make sure I had the Value correct in the formula. No luck.
If I paste the values into the next column(one for testing purposes) and use the Countif to look at that range it works.
I have Counted the results of Index( Match) columns plenty of times and can not figure out what is different in this case.
Has anyone run into this? and what did you do to remedy it?
Thanks,
Robert Meisch
Deployment Operations Manager & Smartsheet Success team
Sysco
Best Answers
-
Are there any cells in the referenced range (the column with the INDEX(MATCH) that say "NO MATCH"? If there's even one cell with a formula error, this will then bubble up to any other formula referencing that column.
Try wrapping an IFERROR around your INDEX(MATCH formula:
=IFERROR(INDEX(...MATCH()), "")
This should translate any errors in that column into a blank cell, so then your COUNTIF formula will skip those cells instead of erroring.
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hey Genevieve,
Thanks for the response! Cleaning up the reference range worked.
Thank you.
Robert Meisch
Answers
-
Are there any cells in the referenced range (the column with the INDEX(MATCH) that say "NO MATCH"? If there's even one cell with a formula error, this will then bubble up to any other formula referencing that column.
Try wrapping an IFERROR around your INDEX(MATCH formula:
=IFERROR(INDEX(...MATCH()), "")
This should translate any errors in that column into a blank cell, so then your COUNTIF formula will skip those cells instead of erroring.
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hey Genevieve,
Thanks for the response! Cleaning up the reference range worked.
Thank you.
Robert Meisch
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!