ARRAYFORMULA with COUNT

user_12882020 0
Viewed 22

im trying to come up with an ARRAYFORMULA to substitute formulas on every row

=IF(ISBLANK(A2);""; COUNTA(FILTER((Derivatives!B:B);INDEX((Derivatives!D:D);0;0)= left(A2;4);INDEX((Derivatives!F:F);0;0)= "EQUITY CALL")))

Exemple

example is almost correct, most of 1 results should be 0, i belive thats caused by COUNTA, not sure, bigger numbers should be correct on my exemple

  • basicly to COUNT number of times Derivatives!B:B, those are UNIQUE
  • WHERE Derivatives!D:D matches LEFT(Example!A2;4),
  • AND Derivatives!F:F is EQUITY CALL
vlookup google-query-language google-sheets-formula google-sheets count
1 Answers

use:

=ARRAYFORMULA(IFNA(VLOOKUP(LEFT(A2:A; 4); 
 QUERY({LEFT(Derivatives!D:D; 4)\ Derivatives!F:F}; 
 "select Col1,count(Col1) where Col2 ='EQUITY CALL' group by Col1"); 2; 0); 0))

enter image description here

user_56326290
Related