Problem #1
I am also trying to find out the number of LATE hours by work center. So if
the work center has some hours logged in but there less than a given date i
want to see them. The formula I'm trying to use is.
=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<"3/2/2009"),$J$2:$J$150)).
This formula does not give me the correct results. column B has the dates,
column D has the work centers and column J has the
hours.
Problem #2
I have entered the dates excluding weekends at the top of each column. I
have the work centers listed in the rows going down the page. I want to be
able to see the number of hours by work center by date.

using excel 2003
--
SRC

=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<" 3/2/2009"),$J$2:$J$150))

Try this normally entered formula.

Use a cell to hold your date criteria:

A1 = 3/2/2009

=SUMPRODUCT(--(D2:D150=Sheet2!A3),--(B2:B150<A1),J2:J150)

Problem #2 = Where do we lookk for the hours worked?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Problem #1
I am also trying to find out the number of LATE hours by work center. So
if
the work center has some hours logged in but there less than a given date
i
want to see them. The formula I'm trying to use is.
=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<"3/2/2009"),$J$2:$J$150)).
This formula does not give me the correct results. column B has the dates,
column D has the work centers and column J has the
hours.
Problem #2
I have entered the dates excluding weekends at the top of each column. I
have the work centers listed in the rows going down the page. I want to be
able to see the number of hours by work center by date.

using excel 2003
--
SRC

Thanks, That solved problem #1.

For problem #2.
The hours I'm looking for are kept in column J. So in the column that has
the work date, I'm trying to see if there are any hours from column J for the
specific work center listed in column D.
I hope that makes sense.
Thanks in advance!
--
SRC


"T. Valko" wrote:

=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<" 3/2/2009"),$J$2:$J$150))

Try this normally entered formula.

Use a cell to hold your date criteria:

A1 = 3/2/2009

=SUMPRODUCT(--(D2:D150=Sheet2!A3),--(B2:B150<A1),J2:J150)

Problem #2 = Where do we lookk for the hours worked?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Problem #1
I am also trying to find out the number of LATE hours by work center. So
if
the work center has some hours logged in but there less than a given date
i
want to see them. The formula I'm trying to use is.
=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<"3/2/2009"),$J$2:$J$150)).
This formula does not give me the correct results. column B has the dates,
column D has the work centers and column J has the
hours.
Problem #2
I have entered the dates excluding weekends at the top of each column. I
have the work centers listed in the rows going down the page. I want to be
able to see the number of hours by work center by date.

using excel 2003
--
SRC

I'm having a hard time trying to picture how your data is setup.

Column D = work center
Column J = hours worked

Where's the date?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Thanks, That solved problem #1.

For problem #2.
The hours I'm looking for are kept in column J. So in the column that has
the work date, I'm trying to see if there are any hours from column J for
the
specific work center listed in column D.
I hope that makes sense.
Thanks in advance!
--
SRC


"T. Valko" wrote:

=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<" 3/2/2009"),$J$2:$J$150))

Try this normally entered formula.

Use a cell to hold your date criteria:

A1 = 3/2/2009

=SUMPRODUCT(--(D2:D150=Sheet2!A3),--(B2:B150<A1),J2:J150)

Problem #2 = Where do we lookk for the hours worked?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Problem #1
I am also trying to find out the number of LATE hours by work center.
So
if
the work center has some hours logged in but there less than a given
date
i
want to see them. The formula I'm trying to use is.
=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<"3/2/2009"),$J$2:$J$150)).
This formula does not give me the correct results. column B has the
dates,
column D has the work centers and column J has the
hours.
Problem #2
I have entered the dates excluding weekends at the top of each column.
I
have the work centers listed in the rows going down the page. I want to
be
able to see the number of hours by work center by date.

using excel 2003
--
SRC

All this date is on Sheet1,
Column B shows the start date
Column C shows the end date
Column D has the work center
Column J has the hours
The other columns on Sheet1 are not coming into play.

The chart showing this schedule is on Sheet2.

Column A shows all the work centers on Sheet2
and row 2 has all the work days listed

--
SRC


"T. Valko" wrote:

I'm having a hard time trying to picture how your data is setup.

Column D = work center
Column J = hours worked

Where's the date?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Thanks, That solved problem #1.

For problem #2.
The hours I'm looking for are kept in column J. So in the column that has
the work date, I'm trying to see if there are any hours from column J for
the
specific work center listed in column D.
I hope that makes sense.
Thanks in advance!
--
SRC


"T. Valko" wrote:

=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<" 3/2/2009"),$J$2:$J$150))

Try this normally entered formula.

Use a cell to hold your date criteria:

A1 = 3/2/2009

=SUMPRODUCT(--(D2:D150=Sheet2!A3),--(B2:B150<A1),J2:J150)

Problem #2 = Where do we lookk for the hours worked?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Problem #1
I am also trying to find out the number of LATE hours by work center.
So
if
the work center has some hours logged in but there less than a given
date
i
want to see them. The formula I'm trying to use is.
=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<"3/2/2009"),$J$2:$J$150)).
This formula does not give me the correct results. column B has the
dates,
column D has the work centers and column J has the
hours.
Problem #2
I have entered the dates excluding weekends at the top of each column.
I
have the work centers listed in the rows going down the page. I want to
be
able to see the number of hours by work center by date.

using excel 2003
--
SRC

Ok, try this on Sheet2...

B2:F2 = dates
A3:A8 = work centers

Enter this formula in B3:

=SUMPRODUCT(--(Sheet1!$B$2:$B$150=B$2),--(Sheet1!$D$2:$D$150=$A3),Sheet1!$J$2:$J$150)

Copy across to F3 then down to row 8.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
All this date is on Sheet1,
Column B shows the start date
Column C shows the end date
Column D has the work center
Column J has the hours
The other columns on Sheet1 are not coming into play.

The chart showing this schedule is on Sheet2.

Column A shows all the work centers on Sheet2
and row 2 has all the work days listed

--
SRC


"T. Valko" wrote:

I'm having a hard time trying to picture how your data is setup.

Column D = work center
Column J = hours worked

Where's the date?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Thanks, That solved problem #1.

For problem #2.
The hours I'm looking for are kept in column J. So in the column that
has
the work date, I'm trying to see if there are any hours from column J
for
the
specific work center listed in column D.
I hope that makes sense.
Thanks in advance!
--
SRC


"T. Valko" wrote:

=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<" 3/2/2009"),$J$2:$J$150))

Try this normally entered formula.

Use a cell to hold your date criteria:

A1 = 3/2/2009

=SUMPRODUCT(--(D2:D150=Sheet2!A3),--(B2:B150<A1),J2:J150)

Problem #2 = Where do we lookk for the hours worked?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Problem #1
I am also trying to find out the number of LATE hours by work
center.
So
if
the work center has some hours logged in but there less than a given
date
i
want to see them. The formula I'm trying to use is.
=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<"3/2/2009"),$J$2:$J$150)).
This formula does not give me the correct results. column B has the
dates,
column D has the work centers and column J has the
hours.
Problem #2
I have entered the dates excluding weekends at the top of each
column.
I
have the work centers listed in the rows going down the page. I want
to
be
able to see the number of hours by work center by date.

using excel 2003
--
SRC

Hi,

For problem, array enter (Ctrl+Shift+Enter) the following formula

=SUM(IF(($D$2:$D$150=sheet2!A3)*($B$2:$B$150<"3/2/2009"),$J$2:$J$150))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
...
Problem #1
I am also trying to find out the number of LATE hours by work center. So
if
the work center has some hours logged in but there less than a given date
i
want to see them. The formula I'm trying to use is.
=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<"3/2/2009"),$J$2:$J$150)).
This formula does not give me the correct results. column B has the dates,
column D has the work centers and column J has the
hours.
Problem #2
I have entered the dates excluding weekends at the top of each column. I
have the work centers listed in the rows going down the page. I want to be
able to see the number of hours by work center by date.

using excel 2003
--
SRC

Hi,

Please change the array formula to:

=SUM(IF(($D$2:$D$150=sheet2!A3)*($B$2:$B$150<sheet 2!A4),$J$2:$J$150)), where
sheet2!A4 holds the date


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
...
Problem #1
I am also trying to find out the number of LATE hours by work center. So
if
the work center has some hours logged in but there less than a given date
i
want to see them. The formula I'm trying to use is.
=SUM(IF(($D$2:$D$150="sheet2!A3")*$B$2:$B$150<"3/2/2009"),$J$2:$J$150)).
This formula does not give me the correct results. column B has the dates,
column D has the work centers and column J has the
hours.
Problem #2
I have entered the dates excluding weekends at the top of each column. I
have the work centers listed in the rows going down the page. I want to be
able to see the number of hours by work center by date.

using excel 2003
--
SRC