COUNTIF doesn't work

  • Thread starter Thread starter KenH
  • Start date Start date
K

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


KenH said:
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.
Click to expand...
 
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)
 
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.
 
Back
Top