Count for the two criterias and Eliminate duplicate

[Sekars]

Hello Friends

If the column B cells have `ABC fatuewea capucino ficas' and if Column K anywhere have Hyphen `-' in cell (some what like *-*) then need to count in light yellow cells after eliminating the duplicates (`|XYZ|100|R-T|' available twice need to count once) by formula.

thanks in advance

Sekar

[Glenn Kennedy]

Try:

=SUM(INDEX(($B$4:$B$21="ABC fatuewea capucino ficas")*( ISNUMBER(SEARCH("-",$K$4:$K$21)))/COUNTIFS($B$4:$B$21,$B$4:$B$21&"",$K$4:$K$21,$K$4:$K$21&""),0))