| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif function
I have a 4 column spreadsheet. Col a = date, Col b = time in, Col c = time
out, Col d = minutes spent. I would like to count all the entries that fall between the hrs of 8:00 AM and 8:59 AM IF there are entries in both column b and c. If there are not entries in both col B and col C, I want to disregard those items. Help would be appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function
=IF(COUNT(B2:C2)=2,SUMPRODUCT(--(B2:B100<=--"8:00"),--(C2:C100=--"8:59")),"")
"jerry" wrote: I have a 4 column spreadsheet. Col a = date, Col b = time in, Col c = time out, Col d = minutes spent. I would like to count all the entries that fall between the hrs of 8:00 AM and 8:59 AM IF there are entries in both column b and c. If there are not entries in both col B and col C, I want to disregard those items. Help would be appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function
"jerry" wrote: I have a 4 column spreadsheet. Col a = date, Col b = time in, Col c = time out, Col d = minutes spent. I would like to count all the entries that fall between the hrs of 8:00 AM and 8:59 AM IF there are entries in both column b and c. If there are not entries in both col B and col C, I want to disregard those items. Help would be appreciated. What column would I paste the ifcount statement in. I forgot to tell folks I was using Excel 2003. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function
Hi,
Try this =SUMPRODUCT(--(B1:B12=--"8:00"),--(C1:C12<--"9:00")) -- Thanks, Shane Devenshire "jerry" wrote: I have a 4 column spreadsheet. Col a = date, Col b = time in, Col c = time out, Col d = minutes spent. I would like to count all the entries that fall between the hrs of 8:00 AM and 8:59 AM IF there are entries in both column b and c. If there are not entries in both col B and col C, I want to disregard those items. Help would be appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function
Try this:
Assuming the cells contain time values only or are empty (no text). =SUMPRODUCT(--(B2:B20=TIME(8,0,0)),--(C2:C20<""),--(C2:C20<=TIME(8,59,0))) Better to use cells to hold the time boundaries: A1 = 8:00 AM A2 = 8:59 AM =SUMPRODUCT(--(B2:B20=A1),--(C2:C20<""),--(C2:C20<=A2)) -- Biff Microsoft Excel MVP "jerry" wrote in message ... I have a 4 column spreadsheet. Col a = date, Col b = time in, Col c = time out, Col d = minutes spent. I would like to count all the entries that fall between the hrs of 8:00 AM and 8:59 AM IF there are entries in both column b and c. If there are not entries in both col B and col C, I want to disregard those items. Help would be appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function
I bet that this...
--(C2:C20<=TIME(8,59,0)) should really be: --(C2:C20<TIME(9,0,0)) But that's a guess! "T. Valko" wrote: Try this: Assuming the cells contain time values only or are empty (no text). =SUMPRODUCT(--(B2:B20=TIME(8,0,0)),--(C2:C20<""),--(C2:C20<=TIME(8,59,0))) Better to use cells to hold the time boundaries: A1 = 8:00 AM A2 = 8:59 AM =SUMPRODUCT(--(B2:B20=A1),--(C2:C20<""),--(C2:C20<=A2)) -- Biff Microsoft Excel MVP "jerry" wrote in message ... I have a 4 column spreadsheet. Col a = date, Col b = time in, Col c = time out, Col d = minutes spent. I would like to count all the entries that fall between the hrs of 8:00 AM and 8:59 AM IF there are entries in both column b and c. If there are not entries in both col B and col C, I want to disregard those items. Help would be appreciated. -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function
That's one of them there "6 of one, half dozen of the other" quandaries!
Although, you do save a couple of keystrokes. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... I bet that this... --(C2:C20<=TIME(8,59,0)) should really be: --(C2:C20<TIME(9,0,0)) But that's a guess! "T. Valko" wrote: Try this: Assuming the cells contain time values only or are empty (no text). =SUMPRODUCT(--(B2:B20=TIME(8,0,0)),--(C2:C20<""),--(C2:C20<=TIME(8,59,0))) Better to use cells to hold the time boundaries: A1 = 8:00 AM A2 = 8:59 AM =SUMPRODUCT(--(B2:B20=A1),--(C2:C20<""),--(C2:C20<=A2)) -- Biff Microsoft Excel MVP "jerry" wrote in message ... I have a 4 column spreadsheet. Col a = date, Col b = time in, Col c = time out, Col d = minutes spent. I would like to count all the entries that fall between the hrs of 8:00 AM and 8:59 AM IF there are entries in both column b and c. If there are not entries in both col B and col C, I want to disregard those items. Help would be appreciated. -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function
I was really going after what happens with 8:59:00 to 8:59:59.999.
"T. Valko" wrote: That's one of them there "6 of one, half dozen of the other" quandaries! Although, you do save a couple of keystrokes. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... I bet that this... --(C2:C20<=TIME(8,59,0)) should really be: --(C2:C20<TIME(9,0,0)) But that's a guess! "T. Valko" wrote: Try this: Assuming the cells contain time values only or are empty (no text). =SUMPRODUCT(--(B2:B20=TIME(8,0,0)),--(C2:C20<""),--(C2:C20<=TIME(8,59,0))) Better to use cells to hold the time boundaries: A1 = 8:00 AM A2 = 8:59 AM =SUMPRODUCT(--(B2:B20=A1),--(C2:C20<""),--(C2:C20<=A2)) -- Biff Microsoft Excel MVP "jerry" wrote in message ... I have a 4 column spreadsheet. Col a = date, Col b = time in, Col c = time out, Col d = minutes spent. I would like to count all the entries that fall between the hrs of 8:00 AM and 8:59 AM IF there are entries in both column b and c. If there are not entries in both col B and col C, I want to disregard those items. Help would be appreciated. -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function
Well, reading the OP they didn't break out the time past 59 mins.
I guess to be on the safe side you could use <TIME(9,0,0). But, I like to try to keep things as "intuitive" as possible. If the actual boundary is 8:59 then that's what I like to use (although there are exceptions). -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... I was really going after what happens with 8:59:00 to 8:59:59.999. "T. Valko" wrote: That's one of them there "6 of one, half dozen of the other" quandaries! Although, you do save a couple of keystrokes. -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... I bet that this... --(C2:C20<=TIME(8,59,0)) should really be: --(C2:C20<TIME(9,0,0)) But that's a guess! "T. Valko" wrote: Try this: Assuming the cells contain time values only or are empty (no text). =SUMPRODUCT(--(B2:B20=TIME(8,0,0)),--(C2:C20<""),--(C2:C20<=TIME(8,59,0))) Better to use cells to hold the time boundaries: A1 = 8:00 AM A2 = 8:59 AM =SUMPRODUCT(--(B2:B20=A1),--(C2:C20<""),--(C2:C20<=A2)) -- Biff Microsoft Excel MVP "jerry" wrote in message ... I have a 4 column spreadsheet. Col a = date, Col b = time in, Col c = time out, Col d = minutes spent. I would like to count all the entries that fall between the hrs of 8:00 AM and 8:59 AM IF there are entries in both column b and c. If there are not entries in both col B and col C, I want to disregard those items. Help would be appreciated. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| please help how to combine IF function with Countif function | Excel Worksheet Functions | |||
| using the countif function | Excel Worksheet Functions | |||
| How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
| Embed a countif function in subtotal function? | Excel Worksheet Functions | |||
| COUNTIF FUNCTION | Excel Worksheet Functions | |||
Linear Mode
