Counting Non-Duplicate Appearances of Text in Cells with Criteria from other columns

[DIAL90]

Hello,

Attached is a outline of What I've been working on. What I want to do is count how many times a colour appears WITHIN the text of the cells in Column B without including duplicates, I think I've managed that with the formula in Column H? But what I can't work out to do is how, in a formula, to filter it further so it e.g. only counts entries in Column B where Column C is 'A' or 'B' and Column D is over 3.

Thanks,

[Bo_Ry]

Please try at I2
=SUMPRODUCT(($B$2:$B$100<>"")*($C$2:$C$100="A")*($D$2:$D$100>3),(ISNUMBER(SEARCH($F2,$B$2:$B$100)))/COUNTIFS($B$2:$B$100,$B$2:$B$100&"",$C$2:$C$100,$C$2:$C$100&""))

or
=SUM(SIGN(FREQUENCY(IF(ISNUMBER(SEARCH(F2,$B$2:$B$100))*($C$2:$C$100="a")*($D$2:$D$100>3),MATCH($B$2:$B$100,$B$2:$B$100,)),ROW($B$1:$B$100))))
and press Ctrl+Shift+Enter

[DIAL90]

Thanks a lot,

Also is there a good way to count 'this' OR 'that' in the same column using this formula, e.g. count all those where value in Column C is 'A' OR 'B'? I know you can just '+' and double up the formula with that one bit changed, but it's a bit long winded.

[Bo_Ry]

1st formula not sure if it count correctly, please test further.

=CEILING(SUMPRODUCT(($B$2:$B$100<>"")*(($C$2:$C$100="A")+($C$2:$C$100="B"))*($D$2:$D$100>3)*(ISNUMBER(SEARCH($F2,$B$2:$B$100)))/COUNTIFS($B$2:$B$100,$B$2:$B$100&"")),1)

I have more confidence on the 2nd formula.

=SUM(SIGN(FREQUENCY(IF(ISNUMBER(SEARCH(F2,$B$2:$B$100))*(($C$2:$C$100="a")+($C$2:$C$100="b"))*($D$2:$D$100>3),MATCH($B$2:$B$100,$B$2:$B$100,)),ROW($B$1:$B$100))))
and press Ctrl+Shift+Enter