countif() with filter() function

Hello,

I helped someone in the below thread, but I wanted to make a more elegant solution that didn't require adding another column.

https://www.excelforum.com/excel-for...ml#post5858297

My attempt to do this is in tab "Solution 2" of the attached file, and the formula is below. ("Solution 1" tab has the accepted solution)
=LET(f,FILTER($B$6:$B$9,$A$6:$A$9=A6),CG,COUNTIF(f,"G"),CH,COUNTIF(f,"H"),C,COUNTA(f),IF(CG=C,"G",IF(CH=C,"H","I")))

I found if I do every step of that formula individually it works out, but combined altogether, it gives the #VALUE! error.

I tracked the error to using the filter function in the countif function.

this formula:
=LET(f,FILTER($B$6:$B$9,$A$6:$A$9=A6),COUNTIF(f,"G"))
returns:
#VALUE!
#VALUE!

is it possible to use the filter function in the countif function, or would some other workaround be required?

Thanks!

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.