SUMIF Formula

[JKVA]

Could someone help me to create a SUMIF formula in Excel 2003 that sums the
employees working at a certain time (i.e. 1500, 1700, 1900)? Each row would
have the employee's time in and time out and the columns would show each day
of the month. If an employee is working that day, there's a "1". The
employees always work the same hours.
Thank You.

 

[ryguy7272]

Good explanation of SUMIF, and some alternatives, here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html#2007

HTH,
Ryan---

 

[Fred Smith]

It would look something like this:
=sumproduct(--(a1:a100="Fred
Smith"),--(b1:b100=1),--(c1:c100<=time(15,0,0)),--(d1:d100)>=time(15,0,0))
Adjust the ranges to suit.

Regards,
Fred.

 

[JKVA]

Very interesting. What if I'm not concerned with the employee name? I'm
basically trying to get a count of all employees working at certain time
periods at the bottom of the spreadsheet for each day of the month. I would
want to count the employees that are working on each day of the month at
0700, 1100, 1730, and 1930. Please let me know if I can give you more info.
Thanks for your help.

 

[JKVA]

Very interesting.
What if I'm not concerned with employee name. I'm just trying to get a
count of how many employees are working at specific time periods (0700, 0900,
1100, 1730, and 1930) based on their time in/time out. I would want to
display this count at the bottom of the spreadsheet under each day of the
month.
Thanks for your help.

 

[Fred Smith]

Then remove the name check, as in:
=sumproduct(--(b1:b100=1),--(c1:c100<=time(15,0,0)),--(d1:d100)>=time(15,0,0))

Regards.
Fred

 

[JKVA]

I definitely tried that, but can't get it to return anything other than 0.
Here's my formula:
=SUMPRODUCT(--(E3:E49=1),--(B3:B49<=TIME(17,30,0)),--C3:C49>=TIME(17,30,0))

Column E has the "1" if an employee is working
Column B has the employees' in time
Column C has the employees' out time

Everything is formatted properly, but something's definitely amiss.

Thanks again for all of the help.

 

[David Biddulph]

Parentheses make a world of difference. You need to apply the double unary
minus to the boolean result of the comparison, not to the time in column C.

Change
=SUMPRODUCT(--(E3:E49=1),--(B3:B49<=TIME(17,30,0)),--C3:C49>=TIME(17,30,0))
to
=SUMPRODUCT(--(E3:E49=1),--(B3:B49<=TIME(17,30,0)),--(C3:C49>=TIME(17,30,0)))