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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!