Countif, Sumif , or Sum Help

[jwalkerack]

Hi there everyone.

What i would like to be able to do is count specific cells in a range that contain text. So i am able to find the total amount of cells that count text in that range

i have copied an Example data set below. i like to count the Comp1 , Comp2 , Comp3 cells for each person. If they have text in them i want to
Count 1 and then add them all up together and put this into the Count Column. i have been trying the countif funcations
But i am not sure how to specifc a range for cells which are single cells

Does any one have any idea

Thanks a lot

Person </SPAN>	Count </SPAN>	Comp1 </SPAN>	Address1</SPAN>	Phone1 </SPAN>	Comp2</SPAN>	Address2</SPAN>	Phone2</SPAN>	Comp3</SPAN>	Address3</SPAN>	Phone3</SPAN>
Jane </SPAN>		adli </SPAN>			beko </SPAN>			Cityin </SPAN>
John </SPAN>		Torry </SPAN>			Apple </SPAN>
Jim </SPAN>		Sony </SPAN>
Tony </SPAN>		nike </SPAN>			Mcdonalds </SPAN>
Steven </SPAN>		Apple </SPAN>			Beko </SPAN>			Timeless</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=9></COLGROUP>

 

[Stridhan]

Try this in B2 and copy down.

=SUMPRODUCT(($C$1:$K$1="comp*")+($C2:$K2<>""))

 

[jwalkerack]

Hi there ,

i tried , that but it did not seem to work , it counted all the Cells in the range and ignored the Comp as being a defining feature. Thanks for your help though

 

[Stridhan]

nope it didnt...

This one

=COUNTIFS($C$1:$K$1,"comp"&"*",$C2:$K2,"<>"&"")

 

[Stridhan]

nope it didnt...

This one

=COUNTIFS($C$1:$K$1,"comp"&"*",$C2:$K2,"<>")

Skip above, use this

=COUNTIFS($C$1:$K$1,"comp"&"*",$C2:$K2,"<>")

 

[jwalkerack]

Hi there , sorry for the late reply . Yeah that seem to do the trick , the second time around. Any ideas where the other two did not work , i am interesting in learning the structure of why it works