=SUMPRODUCT and =IF

[Guest]

I currently use the following to count the number of occurrences that time
shows on my report. This is a great formula.

=SUMPRODUCT((AB3:AB10000>=TIME(6,45,0))*(AB3:AB10000<=TIME(7,29,59)))

What I need in its place is a formula that counts the occurrences of time
only if the cell next to the time shows a 1.

example:

A B
7:54:34 2
8:28:40 1
8:50:16 2
9:32:07 2
10:10:36 2
10:11:02 1

I only want to count a time period only if there is a number one in the next
cell.

Thanks for the help

 

[Guest]

One way

=SUMPRODUCT(--(AB3:AB10000>=TIME(6,45,0)),--(AB3:AB10000<=TIME(7,29,59)),--(AC3:AC10000=1))


assuming the 1 is numeric, if text enclose it in quotations "1"


Regards,

Peo Sjoblom

 

[Guest]

Have you looked at the Countif or Sumif functions?

 

[Guest]

Great solution,

I have use for this formula. One more request. How would it look if we
added one more qualifier. Not only will it count the time only if it meets
the one criteria, I would also like it to meet another criteria in another
cell (for example, the formula you gave me, but add if AD3:AD10000=101.

So it would count the time if it meets the first criteria of 1, then if it
meets the second criteria of 101.

Can you help me with this as well?

Thanks