Replace the addition with cocatenation.
=LEN(C5&E5&G5&I5&K5&M5&O5&Q5&S5)-LEN(SUBSTITUTE(C5&E5&G5&I5&K5&M5&O5&Q5&S5,"1",""))
Just replace "1" with whatever you want to get the count for.Can i get it to count 1's and 3's?
<TABLE cellSpacing=0 cellPadding=6 width="100%" border=0><TBODY><TR><TD class=alt2 style="BORDER-RIGHT: 1px inset; BORDER-TOP: 1px inset; BORDER-LEFT: 1px inset; BORDER-BOTTOM: 1px inset">Originally Posted by mikerickson
Replace the addition with cocatenation.
=LEN(C5&E5&G5&I5&K5&M5&O5&Q5&S5)-LEN(SUBSTITUTE(C5&E5&G5&I5&K5&M5&O5&Q5&S5,"1",""))
</TD></TR></TBODY></TABLE>
Thank you
Just replace "1" with whatever you want to get the count for.
Try this...
Sheet1
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">12</TD><TD style="BORDER-BOTTOM: #000000 1px solid; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">89</TD><TD style="BORDER-BOTTOM: #000000 1px solid; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0.01</TD><TD style="BORDER-BOTTOM: #000000 1px solid; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">1002</TD></TR></TBODY></TABLE>
Count each instance of 1 and 2:
=LEN(C5&E5&G5&I5)-LEN(SUBSTITUTE(SUBSTITUTE(C5&E5&G5&I5,1,""),2,""))
Result = 5[/QUOTE
That works thank you so much can i get it to count d5 f5 h5 as in empty cell =3
Thanks again
If this was the data:Try this...
Sheet1
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">12</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid"></TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">89</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid"></TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">0.01</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid"></TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">1002</TD></TR></TBODY></TABLE>
Count each instance of 1 and 2:
=LEN(C5&E5&G5&I5)-LEN(SUBSTITUTE(SUBSTITUTE(C5&E5&G5&I5,1,""),2,""))
Result = 5
That works thank you so much can i get it to count d5 f5 h5 as in empty cell =3
Thanks again
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | |||
5 | 12 | _ | _ | _ | _ | _ | 1002 | ||
Sheet1 |