ARRAYFORMULA with COUNT

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

>Solution :

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))