google sheetsgoogle-sheets-charts
Instead of having one column for each new "type" below, is there any way to achieve the same result using the non highlighted cells in the image below?
To elaborate, I would like different bar types based on the "type" column, rather than adding a new column for each type.
Is this possible?
The following formula will almost retrieve the table you want.
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'"
)
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".
I've created an example file for you: Counting blank cells based on information in related cell
Please try:
is not equal to A$1 for Range A:A.
It is not case sensitive however.
Best Answer
You can get selected range, using this formula:
The new range can be used to make a chart.