Can someone explain HOW this formula works?

=SUMPRODUCT(--((C$3:C$13<B16)+(D$3:D$13>B15)+(B$3:B$13>C$3:C$13)=3))

I found this formula online, except at the end it was a =2 rather than the =3. I put the =3 in it because it wasn't exactly working the way I needed it to, and once I did that everything seemed to work just fine. But I want to understand how and why it works.

=SUMPRODUCT(--((C$3:C$13<B16)+(D$3:D$13>B15)+(B$3:B$13>C$3:C$13)=3)) IS PLACED IN B16, B20 AND B24



...A.................B.............C..............D
1
2 DATE START STOP
3 BEN............2/8/16......6:00......16:30
4 ANDY..........2/8/16......6:00......16:30
5 JUSTIN........2/8/16......6:00......16:00
6 ROB............2/8/16
7 MELANIE.....2/8/16......6:00......16:30
8 CHANG.........2/8/16......6:00......16:30
9 JOHN...........2/8/16......6:00......16:30
10 PAUL..........2/8/16
11 ANH...........2/8/16
12 MATT..........2/8/16......6:00......15:30
13
14 START.....6:00
15 END.......15:30
16 WORKERS....7
17
18 START....15:30
19 END.....16:00
20 WORKER....6
21
22 START....16:00
23 END......16:30
24 WORKERS....5
25
26
27

Originally Posted by ChemistB

Okay, according to your first post, Start time of 6:00 is in B14 and End time of 15:30 is in B15

You want them to start and end between those times?
You would do better with COUNTIFS
=COUNTIFS(C3:C13>= B14, D3:D13<= B15)
a much simplier formula than the SUMPRODUCT formula

I tried to paste your formula but i'm getting an error.

Originally Posted by ChemistB

Okay, according to your first post, Start time of 6:00 is in B14 and End time of 15:30 is in B15

You want them to start and end between those times?
You would do better with COUNTIFS
=COUNTIFS(C3:C13>= B14, D3:D13<= B15)
a much simplier formula than the SUMPRODUCT formula

I tried to paste your formula but i'm getting an error.

I tried that forumla and it doesn't work correctly. Using my data, I show that 7 employees worked between 6:00 and 15:30- whereas the formula you supplied gives me the number 1.

This has been the hardest formula for be to figure out. LOL!! There has to be an easier way! I have to find a way to be able to put in any timeframe, and based on the timesheet start and end times, have excel tell me how many people worked between that time frame... the search continues....

I tried that forumla and it doesn't work correctly. Using my data, I show that 7 employees worked between 6:00 and 15:30- whereas the formula you supplied gives me the number 1.

This has been the hardest formula for be to figure out. LOL!! There has to be an easier way! I have to find a way to be able to put in any timeframe, and based on the timesheet start and end times, have excel tell me how many people worked between that time frame... the search continues....


SORRY FOR THE DOUBLE POSTS.. SOMETHING IS WRONG WITH THIS FORUM AT THE MOMENT..

For some reason the attachments feature isn't working in the forum, I can't think of a way around it.

Go Figure!!!

Can you see that worksheet? I see it and I can't beleive it came up.

Anyway.. this is what I am working with.

You will see starting at row 15
START
END
Workers

This is where I am typing in the time that I want to look at how many employees worked within that time frame. For example I want to know how many workers worked 6:00-8:00- Almost everyone worked with in that time frame. 8:00-11:30 Only 4 people worked within that time frame. So on and so forth...

Oh and because this thread is getting so long the formula I am currently using is:
=SUMPRODUCT(--((C$3:C$12<B15)+(D$3:D$12>B14)+(B$3:B$12>C$3:C$12)=3))

I have no idea why it's working but it is. Someone mentioned that it's not the right way to do this. I'm looking for a way to do it.

I just realized I keep using .jpgs which is not what you requested.

So I am still playing with this and unfortunately the person that reviews the data wasn't happy with the layout (to busy) so they have asked me to make it a little easier to read. So this is the same type of worksheet, and I am still using the same formula.

I'm still looking to see if anyone beleives there is a different formula to use or if the formula I am using is acceptable (or will it give me problems). I don't understand at all how it's working, but it seems to be working very nicely.

Formula used: =SUMPRODUCT(--((B$4:B$14<C17)+(C$4:C$14>C16)+(B$1>B$4:B$14)=3)) ON CELLS MARKED "Workers" 18,22,26 ETC

This is great!! Thank you so much for the very good explanation and for providing the forumla. I am new to the formulas so it's quite a learning curve.

Thanks again!