On 2002-05-10 14:33, ant wrote:
I've been looking for guys like you!!
Thanks for the suggestions.
I believe my range is correct.
I think Count.if sounds right, but I must be employing it wrong since it is a new function for me.
Basically, I am trying to get a total count; I imagine the process would go:
1) Check the value in the cell.
2) If the value is "", dont count it, otherwise, count it and move to the next cell.
3) At the end of say 25 cells, I should have the total of all the cells with actual values.
Is this something for VBA or can the count.if statement check one cell, store its value, then move to the next...
Thanks again!
<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=5><FONT COLOR=WHITE>
Microsoft Excel - COUNT.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Tool(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>E5</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >
=</TD><TD COLSPAN=2 BGCOLOR=White></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
D</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
1</TD><TD BGCOLOR=#008080 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>Numbers</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#008080 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>Result of Counting</FONT></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=IF(B2,1,"")')><FONT FACE=MS P???? COLOR=#000000></FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=COUNT(A2:A6)')><FONT FACE=MS P???? COLOR=#000000>3</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=IF(B2,1,"")')><FONT FACE=MS P???? COLOR=#000000></FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
6</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>12</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=5><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT>
<FONT COLOR=blue SIZE=1>The above image was automatically written by excel VBA. </FONT><FONT COLOR=blue SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and I'll email the file to you.</FONT>
</CENTER>
This is intended to convince you that COUNT really ignores blanks.
A2 and A5 are blanks, generated using an IF formula.