I have a spreadsheet that looks similar to this (link at the bottom):
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"))