Count Based On Multiple Criteria

  • Thread starter Thread starter Wade
  • Start date Start date
W

Wade

I am trying to count the number of occurrences during a given timeframe for a certain day of the week. I have tried multiple examples that I have found throughout the forums but can't seem to get this to work. Here is an example of the data I am using:


A B C
1 Monday, 9/28/09 12:10 PM
2 Monday, 9/28/09 12:45 PM
3 Monday, 9/28/09 12:57 PM
4 Tuesday, 9/29/09 9:00 AM


The data in the worksheet is for a week at a time. I am trying to count the number of occurrences that happen between a 30 minute time frame on a certain day. For example, the number of occurrences between 12:30 and 12:59 on a Monday would be 2. I have been able to figure them out separately using the following.

Number of occurrences on a Monday:
=SUMPRODUCT(--(WEEKDAY($C$5:$C$100)=2)

Number of occurrences between 12:30 PM and 12:59 PM = SUM(COUNTIF($D$5:$D$100,{">=0.520833333333333",">0.540972222222222"})*{1,-1}


I can't seem to figure out how to combine the two to get a breakdown by day over a certain timeframe. I probably have an extra set of parentheses where I don't need them but I can't figure this out. Any Suggestions?

Thanks,

Wade

EggHeadCafe - Software Developer Portal of Choice
Pete's Real Jewish Nuclear Bomb Holiday Chili
http://www.eggheadcafe.com/tutorial...da-26bad47324de/petes-real-jewish-nuclea.aspx
 
It might be hard to tell from the spacing in the example I gave but Column A has the Occurence Number, Column B has a Date Field, and Column C has the time. Thanks again.





Wade wrote:

Count Based On Multiple Criteria
07-Oct-09

I am trying to count the number of occurrences during a given timeframe for a certain day of the week. I have tried multiple examples that I have found throughout the forums but can't seem to get this to work. Here is an example of the data I am using:


A B C
1 Monday, 9/28/09 12:10 PM
2 Monday, 9/28/09 12:45 PM
3 Monday, 9/28/09 12:57 PM
4 Tuesday, 9/29/09 9:00 AM


The data in the worksheet is for a week at a time. I am trying to count the number of occurrences that happen between a 30 minute time frame on a certain day. For example, the number of occurrences between 12:30 and 12:59 on a Monday would be 2. I have been able to figure them out separately using the following.

Number of occurrences on a Monday:
=SUMPRODUCT(--(WEEKDAY($C$5:$C$100)=2)

Number of occurrences between 12:30 PM and 12:59 PM = SUM(COUNTIF($D$5:$D$100,{">=0.520833333333333",">0.540972222222222"})*{1,-1}


I can't seem to figure out how to combine the two to get a breakdown by day over a certain timeframe. I probably have an extra set of parentheses where I don't need them but I can't figure this out. Any Suggestions?

Thanks,

Wade

EggHeadCafe - Software Developer Portal of Choice
Windows Forms ListBox - 101
http://www.eggheadcafe.com/tutorial...59-157b9b0b7e0b/windows-forms-listbox--1.aspx
 
Use cells to hold your time boundaries:

E2 = 12:30 PM
F2 = 12:59 PM

Then:

=SUMPRODUCT(--(WEEKDAY(B2:B5)=2),--(C2:C5>=E2),--(C2:C5<=F2))
 
Back
Top