How to match #N/A values in csv or excel | Community
Skip to main content
Answered

How to match #N/A values in csv or excel

P

Hello,

How do I count the number of #N/A values in a downloaded CSV or Excel?

Unfortunately, expected functions like =ISNA are  not working matching?

Thank you.

LV

Best answer by john.desborough

@Peter - from what i recall you need to use the COUNTIF() function and put “#N/A” in for the criteria - treat it as a text string… 

this article here says the same thing.. 

 

check to see if that works

 

 

des

View original

4 replies

Liz
Community Team
Forum|alt.badge.img+5
  • Liz
  • Tech Community Advocate
  • 14918 replies
  • July 27, 2022

@john.desborough do you happen to know this by chance? We all know I’m terrible with spreadsheets. 😂

Life is a series of awkward moments separated by snacks.
john.desborough
Forum|alt.badge.img+6
  • john.desboroughCertified Partner
  • Certified Partner & Champion
  • 5274 replies
  • July 27, 2022

@Peter - can you show a screenshot of your spreadsheet and the logic you are trying to use.. that will help me in terms of providing some suggestions ..

 

@Liz - you’re getting so much better with them though lol..c’mon!!

des

If your third coffee of the day is not helping you with your Typeform, maybe we can help you find your answers...
Liz
1 person likes this
  • Liz
    Liz
P
  • Peter
  • Author
  • Explorer
  • 2 replies
  • July 28, 2022

Looks to be some weird problem when downloading in excel format versus CSV format. in excel, the field category is ‘text’ whereas in csv, the field category is ‘general’.

In CSV, using formulas like =COUNTIF(A1:A100,NA()) and =ISNA do in fact work.

But in excel format, they do not.

Hope this helps others.

john.desborough
Forum|alt.badge.img+6
  • john.desboroughCertified Partner
  • Certified Partner & Champion
  • 5274 replies
  • Answer
  • July 28, 2022

@Peter - from what i recall you need to use the COUNTIF() function and put “#N/A” in for the criteria - treat it as a text string… 

this article here says the same thing.. 

 

check to see if that works

 

 

des

If your third coffee of the day is not helping you with your Typeform, maybe we can help you find your answers...
Liz
Gabi Amaral
2 people like this
  • Liz
    Liz
  • Gabi Amaral
    Gabi Amaral

Reply


Still need help?

Don't be shy and ask the community.

Ask your question here!
Community T&Cs