Google Spreadsheet multiple column filter using OR

Question

I have a Google Spreadsheet with 3 columns which are either blank or have a value. I want to get the count of the number of rows that has A and either B or C populated. If I were writing a SQL query it would be

select count(*) 
from Table 
where A is not null and (B is not null or C is not null)

But I can't for the life of me figure out how to get this in a Google Spreadsheet

Answer 1

The formula below should do what you are after:

=ROWS(FILTER(A2:A, NOT(ISBLANK(A2:A)), NOT(ISBLANK(B2:B))+NOT(ISBLANK(C2:C)) ))

And to explain:

• ROWS counts the rows of the argument (filtered, in our case)
• FILTER returns the rows of arg1 (A2:A) that all subsequent arguments match
• The + (addition) symbol combines to predicates with a logical OR

Finally, if you are not using header columns you can change the references from A2:A to A:A

Alternatively, you can use the QUERY function:

(Broken into multiple lines for readability)

=ROWS(QUERY(A2:C, 
    "SELECT A WHERE A IS NOT NULL AND (B IS NOT NULL OR C IS NOT NULL)"))

For more information on the syntax of the queries, see the Visualization API Query Language Reference and specifically the Language Reference