Counting the results of and Index match not functioning...

Robert Meisch

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

Genevieve P.

Hi @Robert Meisch

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

Robert Meisch

Hey Genevieve,

Thanks for the response! Cleaning up the reference range worked.

Thank you.

Robert Meisch

Genevieve P.

Hi @Robert Meisch

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

Robert Meisch

Hey Genevieve,

Thanks for the response! Cleaning up the reference range worked.

Thank you.

Robert Meisch