COUNTIFS Formula in Sheet Summary Returning "#NO MATCH"

Jennifer Whitlock

Hi all,

I had a formula in place yesterday that was working fine, but this morning is returning no match. In our sheet summary, I am trying to count the number of quarantined individuals in a specific market. Here is the formula I was using:

=COUNTIFS(Market1:Market1832, ="NE/IA", Quarantined1:Quarantined1832, <>"")

The markets are filled with a VLOOKUP. My confusion is that it was working yesterday, but is not today. If anypne has any feedback, I would appreciate it.

Thanks

Jennifer Whitlock

I managed to track down the issue, I think. One of the lines in my reference sheet was changed and one of the cells in the column I was running the formula on was now a #NO MATCH. When I corrected this, the entire formula began to work again. I think that if there is an error in the column you are trying to count, the formula returns the same error.

That's my theory, anyway, but I got it working again. Thank you anyway!

Andrée Starå

Hi Jennifer,

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jennifer Whitlock

I managed to track down the issue, I think. One of the lines in my reference sheet was changed and one of the cells in the column I was running the formula on was now a #NO MATCH. When I corrected this, the entire formula began to work again. I think that if there is an error in the column you are trying to count, the formula returns the same error.

That's my theory, anyway, but I got it working again. Thank you anyway!

Andrée Starå

Excellent! 

Happy to help!

Glad you got it working!

Brett Wyrick

https://community.smartsheet.com/discussion/comment/243575#Comment_243575

Thank you so much for posting this as an answer to this. Because I was about to pull my hair out as to why my formulas weren't working - a Google search for something like "#NO MATCH not working formula Smartsheet" led me here. I found the #NO MATCH cells and fixed the error (it was also a VLOOKUP formula within those cells, like yours) - and voila, my COUNTIFS formulas have been fixed.

Thanks again! 👍