Google Sheets – Count Blank Cells Based on Related Cell Information

google sheets

I'm trying to use information from a table to display a count of blank cells based on the type of data in the table. See my example below – I want to count how many of each fruit I'm missing, regardless of their color, and ignoring the apples I do have.

Table:

End result:

Best Answer

The following formula will almost retrieve the table you want.

Formula

 QUERY(data, query, headers)

=QUERY(
   DATA!A1:C7,                                         // data
   "SELECT A, COUNT(B) WHERE C IS NULL GROUP BY A      // query
      LABEL COUNT(B) 'Amount of Empty Types'"             
 )

Explained

Column A is selected to be shown. It is not possible, with the QUERY formula, to perform on the same column a count and select operation. Therefore I performed the count on column B. The where clause on column C is set to contain nothing. All results are to be grouped by column A. At the end of the query, the header/label is changed (otherwise it would show "count Color".

Result

enter image description here

Example

I've created an example file for you: Counting blank cells based on information in related cell

Related Solutions

Google-sheets – Returning the SUM of a range of cells from user-input START to END

Ok - this isn't simple but it can be done. There may be a more elegant way but here's a way that will work, using only the terms you are providing.

Take a range of cells as follows

Col  A   B       C
2    1   300     2
3    2   450     4
4    3   700    
5    4   900    
6    5   1000   
7    6   1200

Take these formulas:

Cell A10: 
=sumif(A2:A7,"<="&C2,B2:B7)

Cell A11:
=sumif(A2:A7,">="&C3,B2:B7)

Cell B10:
=sum(B2:B7)

Cell A12:
=(A10+A11-B10)*-1

Cell A12 will have the results you want. Hopefully you get the idea.

Basically you sum all the data elements you don't want in your range, subtract that from the total sum of the range and the remainder is the sum of the range you want.

I didn't do much bug checking for corner cases, but hopefully this will give you enough of a start to solve the problem?

Google Sheets – How to Count Cells Below a Certain Date and Check for Empty Cells

I think the question asks for a count of rows where the date is before AY12 and the other columns are blank (not populated). It's simpler to "nest" the IF statements, I think - I'd use this version

=ARRAYFORMULA(SUM(if(Mona!$C$4:$C$400<=$AY12,if(Mona!$B$4:$B$400="",if(Mona!$D$4:$D$400="",if(Mona!$E$4:$E$400="",1))))))

Note that blank cells in column C will also be deemed to be <=AY12 so if those might be blank too then you might want to add a check to ensure that that column is not blank, i.e.

=ARRAYFORMULA(SUM(if(Mona!$C$4:$C$400<=$AY12,if(Mona!$C$4:$C$400<>"",if(Mona!$B$4:$B$400="",if(Mona!$D$4:$D$400="",if(Mona!$E$4:$E$400="",1)))))))

Replace commas with semi-colons if your version requires it

Note that your COUNTIF formula quoted above doesn't need the "Arrayformula" part, can simply be this

=COUNTIF(Mona!$F$4:$F$400;"<="&$AY12)