Search code examples
google-sheetsgoogle-sheets-formulaformula

How can I count the values in one column based on the unique values in 2 other columns?

I have a spreadsheet that looks similar to this (link at the bottom):

example spreadsheet

I am trying to find the number of values in column C that match specified criteria based on columns B and C that is unique in column A.

Example 1: If I wanted to find the values of "Air" in column C that matched "Fly" in column B the formula would return 1. Example 2: If I wanted to find the values of "Tree" in column C that matched "Ground" in column B based on unique values in column A the formula would return 2.

I have the following formula:

=COUNTIF(FILTER(B1:B,COUNTIFS(A1:A,A1:A,ROW(A1:A),"<="&ROW(A1:A))=1),"Ground")

But I am unsure of how to refine it so that it works with column C as well. I have tried experimenting with a helper column but this would unfortunately not work with the spreadsheet I am using.

Link to sheet: https://docs.google.com/spreadsheets/d/1u1G8KBwpKpZQ2JMI8k7aRnLil0wLFEuZHZFagziWIeQ/edit?usp=sharing

Solution

  • Try the following formula:

    =LET(u,UNIQUE(A:C),COUNTIFS(INDEX(u,,3),"Tree",INDEX(u,,2),"Ground"))