Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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. .

share|improve this question

1 Answer 1

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.