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
:Two conditions :
A1:A10
must match exactly XB1:B10
must be notblank
Second try :
This count how many unique values in
A:A
have a non empty cell inB:B
next to themEdit : Add a
=SORT
to be surevlookup
match values.