I am currently using this formula in

=COUNTIF($F$6:$F$1194,"Fast") to count cells with the word "Fast",
but
how do we set it up to give a time range as well, example..

Cell A1 to A1000 have various names and one of the them is "Fast".
Cell B1 to B1000 have the delivery times.

I need to have a cell in my report COUNT the number of deliveries in
Column B that were between 00:01 to 00:05 minutes from the service
Fast in Column A

Column A has the name of the service
Column B has time range for the deliveries


Thanks
JP

Hi JP,

See Roger Govier's response to your earlier, similar
post.


---
Regards,
Norman


wrote in message
oups.com...
I am currently using this formula in

=COUNTIF($F$6:$F$1194,"Fast") to count cells with the word "Fast",
but
how do we set it up to give a time range as well, example..

Cell A1 to A1000 have various names and one of the them is "Fast".
Cell B1 to B1000 have the delivery times.

I need to have a cell in my report COUNT the number of deliveries in
Column B that were between 00:01 to 00:05 minutes from the service
Fast in Column A

Column A has the name of the service
Column B has time range for the deliveries


Thanks
JP

You can use a Array formula to handle this. You enter these by
pressing Ctrl+Shift+Enter. Here's an example you can modify for your
needs.
=SUM((A2:A10="Cars")*(B2:B10="Black"))
When you press Ctrl+Shift+Enter, the formula will look like:
{=SUM((A2:A10="Cars")*(B2:B10="Black"))}

wrote:
I am currently using this formula in

=COUNTIF($F$6:$F$1194,"Fast") to count cells with the word "Fast",
but
how do we set it up to give a time range as well, example..

Cell A1 to A1000 have various names and one of the them is "Fast".
Cell B1 to B1000 have the delivery times.

I need to have a cell in my report COUNT the number of deliveries in
Column B that were between 00:01 to 00:05 minutes from the service
Fast in Column A

Column A has the name of the service
Column B has time range for the deliveries


Thanks
JP

Hi

I answered your earlier posting.
You have now changed the requirement.
Enter lowest time in E1, largest time in F1
=SUMPRODUCT(($B$1:$B$1000=E1)*($B$1:$B$1000<=F1)* ($A$1:$A$1000="fast"))

For your future reference, it is better to keep to the original posting,
varying the parameters, rather than starting a new thread.
--
Regards

Roger Govier


wrote in message
oups.com...
I am currently using this formula in

=COUNTIF($F$6:$F$1194,"Fast") to count cells with the word "Fast",
but
how do we set it up to give a time range as well, example..

Cell A1 to A1000 have various names and one of the them is "Fast".
Cell B1 to B1000 have the delivery times.

I need to have a cell in my report COUNT the number of deliveries in
Column B that were between 00:01 to 00:05 minutes from the service
Fast in Column A

Column A has the name of the service
Column B has time range for the deliveries


Thanks
JP

Alternately, you could use SumProduct. Something like:
=SUMPRODUCT((A2:A10="Cars")*(B2:B10="Black"))

JW wrote:
You can use a Array formula to handle this. You enter these by
pressing Ctrl+Shift+Enter. Here's an example you can modify for your
needs.
=SUM((A2:A10="Cars")*(B2:B10="Black"))
When you press Ctrl+Shift+Enter, the formula will look like:
{=SUM((A2:A10="Cars")*(B2:B10="Black"))}

wrote:
I am currently using this formula in

=COUNTIF($F$6:$F$1194,"Fast") to count cells with the word "Fast",
but
how do we set it up to give a time range as well, example..

Cell A1 to A1000 have various names and one of the them is "Fast".
Cell B1 to B1000 have the delivery times.

I need to have a cell in my report COUNT the number of deliveries in
Column B that were between 00:01 to 00:05 minutes from the service
Fast in Column A

Column A has the name of the service
Column B has time range for the deliveries


Thanks
JP

On May 10, 8:18 am, "Roger Govier"
wrote:
Hi

I answered your earlier posting.
You have now changed the requirement.
Enter lowest time in E1, largest time in F1
=SUMPRODUCT(($B$1:$B$1000=E1)*($B$1:$B$1000<=F1)* ($A$1:$A$1000="fast"))

For your future reference, it is better to keep to the original posting,
varying the parameters, rather than starting a new thread.
--
Regards

Roger Govier

wrote in message

oups.com...



I am currently using this formula in

=COUNTIF($F$6:$F$1194,"Fast") to count cells with the word "Fast",
but
how do we set it up to give a time range as well, example..

Cell A1 to A1000 have various names and one of the them is "Fast".
Cell B1 to B1000 have the delivery times.

I need to have a cell in my report COUNT the number of deliveries in
Column B that were between 00:01 to 00:05 minutes from the service
Fast in Column A

Column A has the name of the service
Column B has time range for the deliveries

Thanks
JP- Hide quoted text -

- Show quoted text -

I have entered the Lowest time in e1 as 00:01 and the highest time in
f1 as 00:06 and I get the formula type contains an error.

If i just add a simple number like 1 or 6 I get a value of 0 but no
error

=SUMPRODUCT(($B$6:$B$2500=00:01)*($B$6:$B$2500<=0 0:06)*($A$6:$A
$2500="Fast"))

JP

Hi
It works fine for me. You must have entered it wrongly.
If you do have a time of 00:01 in E1 and a time of 00:06 in F1 then
=SUMPRODUCT(($B$6:$B$2500=E1)*($B$6:$B$2500<=F1)* ($A$6:$A$2500="Fast"))
should work for you also

Otherwise, instead of entering 00:01 and 00:06 in the formula, which
will give a result of 0, try

=SUMPRODUCT(($B$6:$B$2500=TIME(0,1,0))*($B$6:$B$2 500<=TIME(0,6,0))*($A$6:$A$2500="Fast"))

--
Regards

Roger Govier


wrote in message
ups.com...
On May 10, 8:18 am, "Roger Govier"
wrote:
Hi

I answered your earlier posting.
You have now changed the requirement.
Enter lowest time in E1, largest time in F1
=SUMPRODUCT(($B$1:$B$1000=E1)*($B$1:$B$1000<=F1)* ($A$1:$A$1000="fast"))

For your future reference, it is better to keep to the original
posting,
varying the parameters, rather than starting a new thread.
--
Regards

Roger Govier

wrote in message

oups.com...



I am currently using this formula in

=COUNTIF($F$6:$F$1194,"Fast") to count cells with the word "Fast",
but
how do we set it up to give a time range as well, example..

Cell A1 to A1000 have various names and one of the them is "Fast".
Cell B1 to B1000 have the delivery times.

I need to have a cell in my report COUNT the number of deliveries
in
Column B that were between 00:01 to 00:05 minutes from the service
Fast in Column A

Column A has the name of the service
Column B has time range for the deliveries

Thanks
JP- Hide quoted text -

- Show quoted text -

I have entered the Lowest time in e1 as 00:01 and the highest time in
f1 as 00:06 and I get the formula type contains an error.

If i just add a simple number like 1 or 6 I get a value of 0 but no
error

=SUMPRODUCT(($B$6:$B$2500=00:01)*($B$6:$B$2500<=0 0:06)*($A$6:$A
$2500="Fast"))

JP

On May 10, 9:57 am, "Roger Govier"
wrote:
Hi
It works fine for me. You must have entered it wrongly.
If you do have a time of 00:01 in E1 and a time of 00:06 in F1 then
=SUMPRODUCT(($B$6:$B$2500=E1)*($B$6:$B$2500<=F1)* ($A$6:$A$2500="Fast"))
should work for you also

Otherwise, instead of entering 00:01 and 00:06 in the formula, which
will give a result of 0, try

=SUMPRODUCT(($B$6:$B$2500=TIME(0,1,0))*($B$6:$B$2 500<=TIME(0,6,0))*($A$6:$*A$2500="Fast"))

--
Regards

Roger Govier

wrote in message

ups.com...



On May 10, 8:18 am, "Roger Govier"
wrote:
Hi

I answered your earlier posting.
You have now changed the requirement.
Enter lowest time in E1, largest time in F1
=SUMPRODUCT(($B$1:$B$1000=E1)*($B$1:$B$1000<=F1)* ($A$1:$A$1000="fast"))

For your future reference, it is better to keep to the original
posting,
varying the parameters, rather than starting a new thread.
--
Regards

Roger Govier

wrote in message

groups.com...

I am currently using this formula in

=COUNTIF($F$6:$F$1194,"Fast") to count cells with the word "Fast",
but
how do we set it up to give a time range as well, example..

Cell A1 to A1000 have various names and one of the them is "Fast".
Cell B1 to B1000 have the delivery times.

I need to have a cell in my report COUNT the number of deliveries
in
Column B that were between 00:01 to 00:05 minutes from the service
Fast in Column A

Column A has the name of the service
Column B has time range for the deliveries

Thanks
JP- Hide quoted text -

- Show quoted text -

I have entered the Lowest time in e1 as 00:01 and the highest time in
f1 as 00:06 and I get the formula type contains an error.

If i just add a simple number like 1 or 6 I get a value of 0 but no
error

=SUMPRODUCT(($B$6:$B$2500=00:01)*($B$6:$B$2500<=0 0:06)*($A$6:$A
$2500="Fast"))

JP- Hide quoted text -

- Show quoted text -

Thanks the (0,6,0) worked perfectly