Replace ISEMPTY
with ISBLANK
Your formula should be as follows:-
=SUM(FILTER(A:A, ISBLANK(B:B)))
Now you will get the result as 40
Unfortunately filter() unique() and similar are not structured to work on individual cells within a 2 dimensional range, they tend to return rows or columns. The following works as long as you do not skip ranks and every row in the data set is accounted for in the ranking
For this the output goes in A2:C6 and the data is in G2:K6
This formula can be entered into the top cell of the "1st" column, A2, and dragged down the column
=countif($G2:$K2,"="&large($G$2:$K$6,1))
it counts the number of occurrences of the largest number in the data set.
Because largest()
does not provide unique values we need to skip past all of the instances of the highest value to get to the second highest. Fortunately we just counted the instances of the highest value by row. We will take the sum()
of that data to get us to the second largest unique value.
This formula can be entered into the top cell of the "2nd" column, B2, and dragged down the column
=countif($G2:$K2,"="&large($G$2:$K$6,sum(A$2:A$6)+1))
The third rank is similar but we need to sum both of the preceding columns:
=countif($G2:$K2,"="&large($G$2:$K$6,sum(A$2:B$6)+1))
The use of sum()
here is a short cut taking advantage of the way you are structuring your output. A version of the 3rd column formula that can exist independently would look like this(using input range of G31:K35):
=countif($G32:$K32,"="&large($G$31:$K$35,countif($G$31:$K$35,">="&large($G$31:$K$35,countif($G$31:$K$35,"="&large($G$31:$K$35,1))+1))+1))
the nesting of countif()
's and large()
's gets deeper and deeper for each successive rank.
https://support.google.com/docs/answer/3093480?hl=en&ref_topic=3105474
Best Answer
Short answer
Make two queries and use IF, IERROR, ARRAYFORMULA and the array handling feature of Google Sheets.
Extended answer
It's not possible to get the desired result using a single QUERY because of the Google Query implementation. Fortunately the desired result could be obtained by using other Google Sheets features.
From the linked spreadsheet in the question
The following formula returns the desired output:
Remarks: In order to make simple formula the neighbourhood and cuisine are "hardcoded" in the formula. In order to make the formula more easy of maintain, this values could be replaced by cell references but you should be aware that the query argument requires that strings be enclosed by using apostrophes.
Assume that A1 holds the value of the neighbourhood. In the formula, the "hardcoded" value
'Strathcona'
could be replaced by'&A1&'
By the other hand, the case of the cuisine is out of the query argument, so it could be replaced by just the cell reference. Assume that B1 holds de the value of cuisine. In the formula, the "harcoded" value
"Italian"
could be replaced byB1
References
Note to self