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

[TABLE="width: 736"]
<TBODY>[TR]
[TD]Person </SPAN>[/TD]
[TD]Count </SPAN>[/TD]
[TD]Comp1 </SPAN>[/TD]
[TD]Address1</SPAN>[/TD]
[TD]Phone1 </SPAN>[/TD]
[TD]Comp2</SPAN>[/TD]
[TD]Address2</SPAN>[/TD]
[TD]Phone2</SPAN>[/TD]
[TD]Comp3</SPAN>[/TD]
[TD]Address3</SPAN>[/TD]
[TD]Phone3</SPAN>[/TD]
[/TR]
[TR]
[TD]Jane </SPAN>[/TD]
[TD][/TD]
[TD]adli </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]beko </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cityin </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John </SPAN>[/TD]
[TD][/TD]
[TD]Torry </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apple </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim </SPAN>[/TD]
[TD][/TD]
[TD]Sony </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tony </SPAN>[/TD]
[TD][/TD]
[TD]nike </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Mcdonalds </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steven </SPAN>[/TD]
[TD][/TD]
[TD]Apple </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]Beko </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]Timeless</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=9></COLGROUP>[/TABLE]

 

[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