Error using Rows function with a query that returns nothing

Question

I need to count how many "Yes" or "N/A" values there are in a particular column on a sheet, so I have a formula like this:

=Rows(Query(INDIRECT('SheetName'!A:H"), "SELECT * WHERE (Col3=""Yes"" OR Col3 = ""N/A"")",""))

But when the WHERE clause filters out every row I get an error "error: Query completed with an empty output."

When the query's output is empty I'd hope to get a 0 for the "row count". Is there an easy way to do this?

AdamL · Accepted Answer · 2013-08-06 21:04:41Z

3

The easiest way might be to wrap the entire formula in =IFERROR(...;0).

answered Aug 6, 2013 at 21:04

AdamL

24.8k7 gold badges73 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul Sainsbury Over a year ago

Thanks... no idea why I didn't get notified of your answer. As it turns out that's the exact solution I came to a few days later.

Jay Over a year ago

Is there a succinct way to prevent the "#NA" error, and not rescue all other types of errors? You could use IF and ISNA, but then you would have to copy the entire formula if I'm not mistaken.

Matthew · Accepted Answer · 2017-09-21 16:00:45Z

0

The way you're doing it is very roundabout, especially when there is a built-in function for counting the number of matching cells in a column or row: COUNTIF. Also, if you happened to move your source sheet around, your QUERY may stop giving you the correct result. Try this, assuming column C is the one you're after to count how many occurrences of "N/A" and "Yes" appear.

=COUNTIF(C:C,"N/A")+COUNTIF(C:C,"Yes")

answered Sep 21, 2017 at 16:00

Matthew

596 bronze badges

Collectives™ on Stack Overflow

Error using Rows function with a query that returns nothing

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related