Google-sheets – Countunique if cell in same row in other column is not empty

google sheets

Is there a way to calculate the amount of uniques you have in a column with respect to another value not beeing empty. For example:

A    3
B
X    5
Y
A
X    3
Y

This should then result in the answer 2 since X should only be counted once even though the cell is not empty for any. So I think I would need a combination of countunique and some if statement. Or if there is a query solution to the issue.

Tried this but I have not used the query function before so I guess I am not writing it correctly:

=query('ISK Investeringar'!C1:F72; "select C, countunique(C) where (NOT(F=""))")

Bank    70,98   10,11%  120
Bank    94,62   6,87%   
Bank    143,5   9,90%   
Finans  248,8   3,82%   70
Dagligvaruhandel    209,2   3,35%   
Dagligvaruhandel    439,6   2,62%   
Utdelande ETF   125,72  4,45%   60
Investmentbolag 482,1   2,70%   
Investmentbolag 25,76   1,94%   75
Investmentbolag 269,6   3,06%   
Investmentbolag 205,8   2,79%   40
Investmentbolag 130,1   1,92%   

For this sample of values I want the formula to return 4 as a value since there is 1 Bank where F is not "", 1 Finans the same, 1 Utdelande ETF also the same and for "Investmentbolag" only one occurance with an F field not "" should be counted so 4 in total.

Best Answer

To resume your example, you can try COUNTIFS :

=COUNTIFs(A1:A10,"X",B1:B10,"<>")

Two conditions :
A1:A10 must match exactly X
B1:B10 must be not blank


Second try :

=COUNTIFs(
          unique($A$1:$A$10),
          "<>",
          ArrayFormula(vlookup(unique($A$1:$A$10),sort($A$1:$B$10,2,false),2,false)),
          "<>"
)

This count how many unique values in A:A have a non empty cell in B:B next to them

Edit : Add a =SORT to be sure vlookup match values.