# Countunique if cell in same row in other column is not empty

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.

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.

• Yes I have thought of this aswell, however since I require the "X" to be unique instead. This could work if I took all the unique fields and put them in a column and then wanted to calculate the amount of each field. However what I need is basically a functions that looks if a field is not empty, if that field is not empty and the category has not been counted before then +1. So as a script is it possible, however I want to see if there are any possible querys or functions that can make the same calculation. Oct 17, 2019 at 9:51
• @LuckAss I edited my answer : Is this what you're looking ? else you can add some more example in the original question. Edit : I just saw this formula has a big flaw, I'm gonna check for another formula and will update
– pjmg
Oct 17, 2019 at 9:59
• Seems to be in the right direction, however this gives me an error stating that the arrays are different sizes. In my case I have Column C and F which are actually used for the calculation. Oct 17, 2019 at 10:07
• `=COUNTIFs( unique(\$C\$1:\$C\$100), "<>", ArrayFormula(vlookup(unique(\$C\$1:\$C\$100),sort({\$C\$1:\$C\$100,\$F\$1:\$F\$100},2,false),2,false)), "<>" )` You can try this ? else you can share a sheet with dummy data or your formula
– pjmg
Oct 17, 2019 at 10:12
• Getting errors in formula interpretation for that formula, I added some dummy values above Oct 17, 2019 at 10:22