Question for Peo
 

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




"Peo Sjoblom" wrote:

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

"Jim" wrote:

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:AB100 00<=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

If you mean that it will count the times in AB where AC is 1 AND AD is 101

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



Regards,


Peo Sjoblom


"Jim" wrote:

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




"Peo Sjoblom" wrote:

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

"Jim" wrote:

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:AB100 00<=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