| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF doesn't work
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. -- Thanks Ken |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
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" wrote: 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. -- Thanks Ken |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
Which date column is the count based on?
-- Biff Microsoft Excel MVP "KenH" wrote in message ... 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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
Also, will all the dates be within the same year?
-- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Which date column is the count based on? -- Biff Microsoft Excel MVP "KenH" wrote in message ... 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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
this formula assumes your hrs are in colB
=COUNTIF(B:B,"=24") "KenH" wrote: 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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
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. "LXG" wrote: this formula assumes your hrs are in colB =COUNTIF(B:B,"=24") "KenH" wrote: 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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
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. -- Thanks Ken "T. Valko" wrote: Which date column is the count based on? -- Biff Microsoft Excel MVP "KenH" wrote in message ... 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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
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. -- Thanks Ken "LXG" wrote: 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. "LXG" wrote: this formula assumes your hrs are in colB =COUNTIF(B:B,"=24") "KenH" wrote: 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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
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" wrote in message ... 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. -- Thanks Ken "T. Valko" wrote: Which date column is the count based on? -- Biff Microsoft Excel MVP "KenH" wrote in message ... 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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
They go to the month listed in Col. A
-- Thanks Ken "T. Valko" wrote: 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" wrote in message ... 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. -- Thanks Ken "T. Valko" wrote: Which date column is the count based on? -- Biff Microsoft Excel MVP "KenH" wrote in message ... 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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
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) -- Biff Microsoft Excel MVP "KenH" wrote in message ... They go to the month listed in Col. A -- Thanks Ken "T. Valko" wrote: 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" wrote in message ... 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. -- Thanks Ken "T. Valko" wrote: Which date column is the count based on? -- Biff Microsoft Excel MVP "KenH" wrote in message ... 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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF doesn't work
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. -- Thanks Ken "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "KenH" wrote in message ... They go to the month listed in Col. A -- Thanks Ken "T. Valko" wrote: 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" wrote in message ... 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. -- Thanks Ken "T. Valko" wrote: Which date column is the count based on? -- Biff Microsoft Excel MVP "KenH" wrote in message ... 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. -- Thanks Ken "JVMan" wrote: 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" wrote: 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. -- Thanks Ken |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to get Countif to work this out? | Excel Worksheet Functions | |||
| Countif does not work | Excel Worksheet Functions | |||
| CountIf should work across sheets | Excel Worksheet Functions | |||
| SUMPRODUCT vs. COUNTIF -- Why does one work? | Excel Worksheet Functions | |||
| countif(a1:a12,">TODAY()") How do I get this to work? | Excel Worksheet Functions | |||
Linear Mode
