Need a formula with SUMPRODUCT function
 

Hi,

Please look the below example and tell me any formula that can be used to
fulfil my requirement.

Example:

9:01:55 ABC Yes
9:04:15 ABC Yes
9:27:00 XYZ No
9:29:57 XYZ No
9:30:45 ABC Yes
9:35:13 ABC Yes
9:53:14 XYZ No
9:57:07 XYZ No

Three different columns, where the

first column has range of cells with time format from 0:00:00 to 24:00:00
second column has range of cells with two different text strings ABC and XYZ
third column has range of cells with two different text strings Yes and No

Need to know a formula which counts the total number of ABC and Yes in each
time slot.. (0:00:00 - 0:59:59, 1:00:00 - 1:59:59, .... so on)

The formula im trying to use is..

=SUMPRODUCT(--(A1:A100="=9:00:00<=9:59:59"),--(B1:B100="ABC"),--(C1:C100="Yes"))

Hope the explanation is clear..

Thanks in advance

Sasikiran

Need a formula with SUMPRODUCT function
 

Try it like this:
=SUMPRODUCT((A1:A100=--"9:00:00")*(A1:A100<--"10:00:00")*(B1:B100="ABC")*(C1:C100="Yes"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sasikiran" wrote:
Hi,

Please look the below example and tell me any formula that can be used to
fulfil my requirement.

Example:

9:01:55 ABC Yes
9:04:15 ABC Yes
9:27:00 XYZ No
9:29:57 XYZ No
9:30:45 ABC Yes
9:35:13 ABC Yes
9:53:14 XYZ No
9:57:07 XYZ No

Three different columns, where the

first column has range of cells with time format from 0:00:00 to 24:00:00
second column has range of cells with two different text strings ABC and XYZ
third column has range of cells with two different text strings Yes and No

Need to know a formula which counts the total number of ABC and Yes in each
time slot.. (0:00:00 - 0:59:59, 1:00:00 - 1:59:59, .... so on)

The formula im trying to use is..

=SUMPRODUCT(--(A1:A100="=9:00:00<=9:59:59"),--(B1:B100="ABC"),--(C1:C100="Yes"))

Hope the explanation is clear..

Thanks in advance

Sasikiran

Need a formula with SUMPRODUCT function
 

Thanks a ton Max... its working fine

"Max" wrote:

Try it like this:
=SUMPRODUCT((A1:A100=--"9:00:00")*(A1:A100<--"10:00:00")*(B1:B100="ABC")*(C1:C100="Yes"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sasikiran" wrote:
Hi,

Please look the below example and tell me any formula that can be used to
fulfil my requirement.

Example:

9:01:55 ABC Yes
9:04:15 ABC Yes
9:27:00 XYZ No
9:29:57 XYZ No
9:30:45 ABC Yes
9:35:13 ABC Yes
9:53:14 XYZ No
9:57:07 XYZ No

Three different columns, where the

first column has range of cells with time format from 0:00:00 to 24:00:00
second column has range of cells with two different text strings ABC and XYZ
third column has range of cells with two different text strings Yes and No

Need to know a formula which counts the total number of ABC and Yes in each
time slot.. (0:00:00 - 0:59:59, 1:00:00 - 1:59:59, .... so on)

The formula im trying to use is..

=SUMPRODUCT(--(A1:A100="=9:00:00<=9:59:59"),--(B1:B100="ABC"),--(C1:C100="Yes"))

Hope the explanation is clear..

Thanks in advance

Sasikiran

Need a formula with SUMPRODUCT function
 

welcome, good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sasikiran" wrote in message
...
Thanks a ton Max... its working fine