Work center hours

[Steve]

I am trying to calculate several different things. The first is the number
of hours for specific work centers.
The data is on a separate worksheet that has 10 separate columns and
multiple rows.
Column D shows the work center and column J shows the hours. I am using the
following formula. =SUMIF(sheet1!$D$2:$D$150,sheet2!A3,sheet1!$J$2:$J$150)
and it works.
In the above formula sheet2 A3 is the name of one of the work centers.

Now for the problems. 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)).
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.

PLEASE HELP

 

[~L]

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

The second condition: *$B$2:$B$150<"3/2/2009"
"3/2/2009" is evaluated as text, therefore < is not a valid comparison (it
will return true when comparing number < text). If your dates are stored as
text, convert them to numbers (DATEVALUE might help) first and use:
*$B$2:$B$150<Date(2009,3,2)

Do array enter this.

Problem 2:

=sumproduct(--(date range=date value),--(work center range=work center
value),hours range)

More specifically, with the data in a separate data page where column A has
dates, column B has work centers and column C has hours, on a summary page
with work centers down column A and dates across row 1, in cell B2:

=sumproduct(--(data!$A$2:$A$5000=B$2),--(data!$B$2:$B$5000=$A2),data!$C$2:$C$5000)

No need to array enter this.

then drag down and right.