SUMIF Formula
 

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.

SUMIF Formula
 

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

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JKVA" wrote:

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.

SUMIF Formula
 

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" wrote in message
...
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.

SUMIF Formula
 

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.



"Fred Smith" wrote:

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" wrote in message
...
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.

.

SUMIF Formula
 

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" wrote:

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" wrote in message
...
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.

.

SUMIF Formula
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.

.

SUMIF Formula
 

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.

"Fred Smith" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.

.


.

SUMIF Formula
 

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)))
--
David Biddulph

"JKVA" wrote in message
...
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.

"Fred Smith" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.

.


.