1

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.

0

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. – LuckAss Oct 17 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 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. – LuckAss Oct 17 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 at 10:12
  • Getting errors in formula interpretation for that formula, I added some dummy values above – LuckAss Oct 17 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.