# Counting the numbers from letters

I have a worksheet that has these inputs:

``````0900-1800
0900-1800
DAYOFF
0900-1800
0900-1800
``````

my question is how can I count only the ranges with the time only? like for example the data above should have the count 4 because it only has 4 days with numbers. .

-

You can add a second column with a formula of the form

``````=IF(ISNUMBER(VALUE(LEFT(A1,1))),1,0)
``````

which will determine if the first character of the cell `A1` is a numeric character, which in your example would correspond to a workday. Workdays will be represented by `1` in the new column, other days by `0`. Note that this formula assumes that your workdays always start the field with a number, and free days never do!

You can then sum over this new column to get the total number of workdays.

Option 2

As mentioned in the comments, you can try to use the function `COUNTIF` which in principle allows to count cells with certain characteristics without the need for a temporary column. The problem is that AFAIK you can't use the full flexibility of individual formulas, so I can't come up with a version checking explicitly for cells starting with a numeric character. I found a solution which works for your example though:

``````=COUNTIF(A1:A7;"<A")
``````

which (I think) counts cells whose first character has a smaller ASCII code than the letter "A". Note that this includes a lot of non-letter characters, notable the `space`!

Option 3 includes using an array formula, inspired by a Tip of the Day on MrExcel.com:

``````=SUM((A1:A5>="0")*(A1:A5<="9")*1)
``````

This needs to be entered as an array formula, i.e. by pressing `Ctrl+Shift+Enter` instead of just Enter. This solution is equivalent to the first one I gave, but avoiding the extra column. Note that the extra column still has the advantage that you can manually verify for each day individually that it was classified correctly.

-
Expand on this for a second. Without the extra column, you can summarize all this into one cell using `=COUNTIF(A1:A5,ISNUMBER(VALUE(LEFT(A1,1))))` –  Theo Apr 9 '11 at 15:54
@Theo I agree that countif() is often a nice way to avoid extra columns. The formula you gave doesn't seem to work though - it would try to compare the full cell values of A1 through A5 with the boolean value determined for cell A1 and count how often that matches... I'm not saying you can't make COUNTIF work here, but I don't see a straightforward way of doing it (without stronger assumptions on the data) –  Jonas Heidelberg Apr 9 '11 at 23:14
@Theo: I added two more answers - now also including one with `COUNTIF`:-) –  Jonas Heidelberg Apr 10 '11 at 16:19