SUM(IF greater/equal every other row

[Defaced]

Hopefully I will explain this well enough to understand outside my brain. I have 2 columns of shift times (Shift Start and Shift End) all on the even rows. Under their shifts is the row that has their Lunch break start/end times. I need to get an interval count of the number of people staffed, but when I used:

{=SUM(IF($A3>=$B$3:$B$100,IF($A3<$C$3:$C$100,1,0),0))}

It includes the lunch shifts and makes my counts inaccurate. Does anyone know of a way to only sum the even rows (shifts) and subtract the even rows (lunches) to get a true count?

 

[Jonmo1]

Do you have any key values in another column that could indicate which rows are Shift times, and which are Lunch times ?
So you could say sum this if column A equals "shift" ?

 

[Defaced]

No they don't, just the two columns that have Start/End times:

[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl69, width: 64"]Start[/TD]
[TD="class: xl69, width: 64"]End[/TD]
[/TR]
[TR]
[TD="class: xl68"]MSR A[/TD]
[TD="class: xl71"]8:00[/TD]
[TD="class: xl71"]4:30[/TD]
[/TR]
[TR]
[TD="class: xl70"]Lunch[/TD]
[TD="class: xl71"]12:00[/TD]
[TD="class: xl71"]12:30
[/TD]
[/TR]
</tbody>[/TABLE]

Similar to that with MSR A and Lunch in column A, Start in Column B and End in Column C. MSR A is Row 2 and Lunch is Row 3.

 

[Jonmo1]

Is that what it looks like in your book? That sure looks like column A could be used as a criteria, sum the ones that don't = Lunch.

 

[Defaced]

What I have is a sheet that has a list of 30 minute intervals:

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"]6:00 AM[/TD]
[/TR]
[TR]
[TD="class: xl67"]6:30 AM[/TD]
[/TR]
[TR]
[TD="class: xl67"]7:00 AM[/TD]
[/TR]
[TR]
[TD="class: xl67"]7:30 AM

[/TD]
[/TR]
</tbody>[/TABLE]
And so forth, that is all in column A (Summary Tab). The data in the previous comment (Start End) is on other tabs and I am trying to get a count of how many people I would have staffed at 6:00 AM based on their Start time, but I do not want it to include any lunches that are listed as 6:00 AM. So If I had 3 people that start at 6:00 AM and 1 person lunch at 6:00 AM, the total should be 2 and not 4.

 

[Jonmo1]

Just based on the original formula, to check EVEN rows

=SUM(IF($A3>=$B$3:$B$100,IF($A3<$C$3:$C$100,IF(ISEVEN(ROW($B$3:$B$100)),1,0),0),0))

 

[Defaced]

It's still not showing correctly for lunches. This is what I am trying to get and will use a short example. I have a schedule and lunch that looks like this:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"]B[/TD]
[TD="class: xl67, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl68"]6:00 AM[/TD]
[TD="class: xl68"]2:30 PM[/TD]
[/TR]
[TR]
[TD="class: xl69"]11:00 AM[/TD]
[TD="class: xl69"]11:30 AM
[/TD]
[/TR]
</tbody>[/TABLE]

Start time is column B, end time is column C. Since this person is working 6-2:30, everything next to the time should have a 1 except the 11:00 cell on the Summary Tab:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"]A[/TD]
[TD="class: xl67, width: 64"]B[/TD]
[/TR]
[TR]
[TD="class: xl68"]6:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]6:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]7:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]7:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]8:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]8:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]9:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]9:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]10:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]10:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]11:00 AM[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl68"]11:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]12:00 PM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]12:30 PM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]1:00 PM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]1:30 PM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68"]2:00 PM[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

With the formula you provided it has a 1 next to everything from 6-2:00

 

[Jonmo1]

It's covering 2 different sheets?

You need to reference the sheet names.
On the Summary sheet, use the formula like this and reference the sheet with the schedules (you didn't say the name of that Sheet, I'm guessing Schedule)

=SUM(IF($A3>=Schedule!$B$3:$B$100,IF($A3 < Schedule!$C$3:$C$100,IF(ISEVEN(ROW(Schedule!$B$3:$B$100)),1,0),0),0))

 

[Defaced]

That's exactly what I had and it's still counting the lunch at 11:00. Is there a way with that formula to subtract using ISODD? This way it will read the 11:00 lunch and subtract it so that cell will show as a 0 instead of a 1.

 

[Jonmo1]

Change iseven to isodd if needed. Whichever you want included.