Need a formula with SUMPRODUCT function

S

Sasikiran

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
 
M

Max

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

Sasikiran

Thanks a ton Max... its working fine

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top