Need a formula with SUMPRODUCT function

[Guest]

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

 

[Guest]

Try it like this:
=SUMPRODUCT((A1:A100>=--"9:00:00")*(A1:A100<--"10:00:00")*(B1:B100="ABC")*(C1:C100="Yes"))

 

[Guest]

Thanks a ton Max... its working fine

Try it like this:
=SUMPRODUCT((A1:A100>=--"9:00:00")*(A1:A100<--"10:00:00")*(B1:B100="ABC")*(C1:C100="Yes"))

 

[Max]

welcome, good to hear that.