I have a Google Spreadsheet with 3 columns that 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
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+ (addition) symbol combines two predicates with a logical ORFinally, 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
=SUMPRODUCT(((A:A<>"")*((B:B<>"")+(C:C<>"")))>0)
if there is only one argument for SUMPRODUCT() it works just as SUM(ARRAYFORMULA(N( )))
I m making a script that will populate a Google Sheet with info about our GA4 analytics properties. It starts by listing all our accounts, then looping through them and listing any GA4 properties in ...
I need an urgent help i have data as follows in a google sheet i need to add a year counter to the column d as follows using google apps script
I have a Google Spreadsheet with 3 columns that 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 ...
If I create a Google apps script, can I hook it up to Google spreadsheet to run based on an event, or must I manually invoke it? I don t know if Google Sheets supports any events. For example: a ...
I am working with a small theatre company. Currently they have a list of people on paper with notes about their skills next to each one. I want to create a database / directory for them so that they ...
I ve seen; Accessing Google Spreadsheets with C# using Google Data API and http://code.google.com/apis/spreadsheets/data/2.0/developers_guide_dotnet.html#CreatingRows However i m still having ...
I have this code which is working, to load a Google Spreadsheet and load some data from it. If the spreadsheet in question is public, how do i modify the code to not require a username/password? $key=...
I ve got a pre-existing Google spreadsheet. Each month I update this document. I ve got a template workseet in the spreadseet that I d like to clone and then update. I d prefer to clone the ...