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.

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.

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.

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.

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.

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

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

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

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