COUNTIF doesn't work

[KenH]

I want col. "E" to count how often col "C" <=24hrs for each of the 12 months

A B C D
E
Date & time Date & Time Elapsed time Time <= 24:00hr TALLY
Rec'd Needed
1/2/'09 13:00 1/3/'09 09:00 20hrs JAN
2
1/2/09 14:00 1/3/'09 10:00 20hrs FEB
0
1/2/09 14:00 1/4/'09 14:00 48hrs MAR
0
2/2/'09 09:00 2/2/'09 10:00 25hrs

The solution evades me.

 

[JVMan]

Ken, I believe you would need to remove the "hrs" from your values. I
believe EXCEL will view that as a text, thus not being able to make the
compare.

 

[KenH]

OOPS! That was an error in my example.
I did it for clarity. It doesn't actually show in the works sheet. Neither
does the " ' " in the year.

Sorry for all of the unneeded confusion. Thanks for bringing it to my
attention.

 

[T. Valko]

Which date column is the count based on?

 

[T. Valko]

Also, will all the dates be within the same year?

 

[LXG]

this formula assumes your hrs are in colB
=COUNTIF(B:B,">=24")

 

[LXG]

also if you actually do have 23hrs in A2 then enter this column to extract
the 23
=VALUE(LEFT(A2,FIND("h",A2)-1))...then use countif below

If this is an acceptable solution please click yes below.

 

[KenH]

The formula in C2 is =B2-A2.

Then I'm trying to count the times that values in col. C are <=24 for each
of the 12 months. In my example it would be 2 for Jan. , 0 for Feb. etc.

I'm realizing that I certainly didn't state my dilemma clearly - apologies
for that.

 

[KenH]

The value in col C is = B2-A2. Then I'm trying to count the number of times,
in col. D,that the value in col. C is <= 24:00 hours for each of the 12
months.

 

[T. Valko]

Ok, but what month do the hours get charged to if/when the dates span more
than one month (if that's possible)? For example:

1/31/2009 15:00 to 2/1/2009 15:00 ... 24hrs

Do those hours get charged to Jan or Feb?

--
Biff
Microsoft Excel MVP

The formula in C2 is =B2-A2.

Then I'm trying to count the times that values in col. C are <=24 for each
of the 12 months. In my example it would be 2 for Jan. , 0 for Feb. etc.

I'm realizing that I certainly didn't state my dilemma clearly - apologies
for that.

 

[KenH]

They go to the month listed in Col. A

 

[T. Valko]

Dates in the range A1:A20

E1:E12 = month names as TEXT strings in the form mmm (Jan, Feb, Mar, Apr,
etc)

Enter this formula in F1 and copy down to F12:

=SUMPRODUCT(--(TEXT(A$1:A$20,"mmm")=E1),C$1:C$20)

 

[KenH]

I apologize for not responding more quickly. I'm still not getting the
results I'm looking for, so I'm double checking my formatting etc. Since I am
interrupted fairly often, it may be a while. Your efforts are appreciated.