I am usually really good at figuring out formulas but I am kind of stuck on this one.

I have a HUGE spreadsheet that I use to complete a schedule for employees. They can be scheduled in different areas at different times. I am trying to write a formula that will count the amount of people in an area that start at or before a certain time.

Example in cells a11 through c15
A10=AREA B10=Shift Start C10=Shift End
A11=DD B11=0600 C11=1400
A12=DD B12=1400 C12=1900
A13=hr B13=0600 C13=1200
A14=DD B14=0700 C14=1500
A15=rr B15=1700 C15=2200

So you probably figured out the columns b and c are in military time format.

I am trying to write a formula that will count all of the people scheduled in area DD that are scheduled at or before 0700. I will be writing this formula for each area code and will make another one for associates that close (leave last)

I am aware of the sumif, and countif but they only give me the ability of 1 criteria. I want to stay away from VBA unless I have to and I would like to not have to use the sumproduct formula since it slows things down when there are alot of them. (we are talking around 400 formulas)



Thanks

The bormula will be based on SUMPRODUCT, but you must upload a sample data to see the format used, especially for that military time, is it entered as text, or the column is formatted as date "hhmm" ?

This formula assumes that you entered time in a cell formatted as text, and in G2 is the refference time, in format 16:00 PM
=SUMPRODUCT((TIME(LEFT(B11:B15;2);RIGHT(B11:B15;2) ;0)<=G2)*1)
Or, if you keep the same format in G2, simply: =SUMPRODUCT((B11:B15<=G2)*1)
To take into account the area, entered in F2: =SUMPRODUCT((TIME(LEFT(B11:B15;2);RIGHT(B11:B15;2) ;0)<=G2)*(A11:A15=F2))
Note: it is not an array formula, confirmed with CSE, so so it is not consuming so much resources. For another approach, using COUNTIF
=COUNTIF(IF(B11:B15<=G2;A11:A15);F2)
The red formula, passes to countif a range with only the rows that qualifies to the time criteria, so now you have a COUNTIF with 2 criteria...

I really like the countif approach. It does not quite work though. I changed the semi colons to commas and that got it to function but it is not counting just the cells with the desired before time it just counts the amount of times the area appears. I attached a sample so you can see the result. Again the numbers are all formatted as a number so a simplified sumproduct formula I guess could work but I really do like the countif formula.

Thanks so much for your help so far...

Attached Files

area counter test.xls (15.0 KB, 12 views)

My apologies. The format is in just a custom number with 0000 in order to show the 0 before. The number is calculated in a different table with vertical lookup. The number appears exactly as written and formula isnumber for the military time format = true.

Perhaps

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Last time I used this technique it slowed the spreadsheet way down. I just entered them all and it seems to be working fine with it. Thanks so much

Did you use large ranges hat time ? Like A1:A65535 ? That would slow thing down
If necessary Dynamic ranges are best

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

You know I think I did. It was shortly after I discovered the sum if formula which was much more effective for the application I was doing. But it is evident now that the sumproduct formula is exactly what I needed. Thanks again I will mark as solved.