0

I have three functions responsible for checking if I own a stock (by seeing that the column F is not empty) Then I want to check column AB to see what market the stock is from. In the first case I want to calculate the amount of Large Cap companies I own stocks in.

However my formulas seem to recalculate incorrectly. Sometimes they do not change at all when I add new owned stocks on my list. The following three functions are the functions I am using. (Keep in mind that it is European separators so the ";" should for example not be changed to ",".

=COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103\'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "Large Cap" )/D3

=(COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103\'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "Mid Cap") + COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103\'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "Small Cap"))/D3

=COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103\'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "First North" )/D3

With these 3 formulas and for example with 7 different stocks in the following markets:

Large Cap     
Large Cap     
Large Cap     
Large Cap     
ETF
Mid Cap
First North

I get the following percentages:

71% Large Cap
14% Mid Cap
0% First North

Can anyone see what is incorrect in this formula for the purpose described above. I have the same type of formula for looking at how many unique branches I own companies in and that function works perfectly. I will share that function below:

=COUNTIFs( UNIQUE('ISK Investeringar'!$C$3:$C$103); "<>"; ArrayFormula(vlookup(unique('ISK Investeringar'!$C$3:$C$103);sort({'ISK Investeringar'!$C$3:$C$103\'ISK Investeringar'!$F$3:$F$103};2;false);2;false)); "<>" )

So I would like to know why my first 3 formulas is not working correctly but the last one is.

Link to the worksheet

https://docs.google.com/spreadsheets/d/1gxOsNvpw4iWejj4nzFmOsufIzrbwfqY0jbNhN2HCn4o/edit?usp=sharing

Thanks in advance

3
  • 1
    Please share a copy of your spreadsheet
    – Tedinoz
    Nov 8, 2019 at 10:10
  • @Tedinoz +1, furthermore I think it's better to use a query because the formulas are getting really complicated and difficult to read.
    – pjmg
    Nov 8, 2019 at 14:28
  • @Tedinoz I have added a share link to the sheet now
    – LuckAss
    Nov 11, 2019 at 7:28

1 Answer 1

0

Here's a query who count of the occurence in column A with non empty data on column B :

=QUERY(A1:B10;
  "SELECT A,count(B) 
  WHERE NOT B matches '' 
  Group by A 
  label count(B) 'non-empty rows'")

Illustration


Example with this formula :

=COUNTIFs(
('ISK Investeringar'!$F$3:$F$103); ""<>"" ; 
ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);
sort({'ISK Investeringar'!$F$3:$F$103\'ISK Investeringar'!$AB$3:$AB$103};2;false);
2;false))
; ""Large Cap"" )/D3

Here's a Query that does the same job :

=QUERY(
{'ISK Investeringar'!$F$3:$F$103\'ISK Investeringar'!$AB$3:$AB$103};
"SELECT Col1,count(Col2) 
WHERE NOT Col2 matches '' AND Col1 = 'Large Cap' 
Group by Col1 
Label count(Col2) 'non-empty rows' "
)/D3
3
  • This query function seems to throw me an error on line 5 column 1. QUERY parametern 2: PARSE_ERROR: Encountered " ")" ") "" at line 5, column 1. Was expecting one of: <EOF> "format" ... "options" ... "," ... Huge thanks for all your help, now and previously :)
    – LuckAss
    Nov 11, 2019 at 7:27
  • I forgot to close the SQL statement with "
    – pjmg
    Nov 11, 2019 at 9:03
  • 1
    Big thanks, this solution works perfectly when switching Col1 and Col2 in the formula. Huge thanks, you save my day yet again sir.
    – LuckAss
    Nov 11, 2019 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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