Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interest Accrual Question | Excel Discussion (Misc queries) | |||
Advanced Window Split & Freeze Question | Excel Worksheet Functions | |||
COUNTIF Question | Excel Worksheet Functions | |||
Have a question on scrolling sum with Excel. | Excel Worksheet Functions | |||
Have a question on scrolling sum with Excel. | Excel Worksheet Functions |