SUM & CountIF functions

[j3wb0y]

Hey everyone!

I am hoping someone can be of assistance with my dilemma. I am currently using the formula below:

=SUM(COUNTIF($B$1:$H$13,"Keith T")*0.5)+SUM(COUNTIF($B$1:$H$13,"Keith A"))

The problem with the formula above is that it is subtracting the count if I place more than one String in a single cell. For instance, Cell A5 may have "Keith T" and "Tom T" in the same cell separated by a comma or ALT+ENTER.

Would anyone know a way to prevent this from happening?

Thank you,

Josh

 

[krykm]

=SUM(COUNTIF($B$1:$H$13,"*Keith T*")*0.5)+SUM(COUNTIF($B$1:$H$13,"*Keith A*"))

Hoppe it helps

 

[Aladin Akyurek]

1)

=SUM(COUNTIFS($B$1:$H$13,{"*Keith T*","*Keith A*"})*{0.5,1})

2) If you use cell references...

=SUMPRODUCT(--(I1:J1<>""),COUNTIFS($B$1:$H$13,"*"&I1:J1&"*"),{0.5,1})

where I1 = Keith T and J1 = Keith A.

 

[j3wb0y]

Thank you! I went with Aladin's formula to simplify it. Krykm, yours worked as well so thank you!