How can I use the SUMIF function using multiple criteria

[Steve]

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

 

[T. Valko]

=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(--(D2150=Sheet2!A3),--(B2:B150<A1),J2:J150)

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

 

[Steve]

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!

 

[T. Valko]

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?

 

[Steve]

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

 

[T. Valko]

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.

 

[Steve]

That worked, thanks a lot, your awesome!

While were on a roll maybe you would know how to make this work.

Can that same formula be modified so it splits the hours up equally from
start to end date? The formula currently puts the total number of hours in
the date column based on the start date that was shown in column B.

column A Column B Column C Column D
Row 1 Start date End date work center total hours
Row 2 3/2/09 3/4/09 18 Bench 10.0
Row 3 3/4/09 3/12/09 62 Haas 100.0
Row 4 3/5/09 3/12/090 SAW 12.0

Currently when you look at the work load on 62 Haas it would show you that
on 3/4/09 there is 100.0 hours of work. I would like to have it broken up by
the number of work days between 3/4/09 and 3/12/09. So it would take 6
working days (not counting weekends) and then divide the number of hours into
those 6 days so the schedule would show 16.66 hours each day starting 3/4/09
and ending 3/12/09.

Thanks,

 

[T. Valko]

Instead of doing all that in one formula why not just add another column to
your table?

Column E = Avg Hrs

The formula would be:

=D3/(NETWORKDAYS(A3,B3)-1)

 

[Ashish Mathur]

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

 

[Ashish Mathur]

Hi,

Please change the array formula to:

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


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Steve]

I've added a column for average hours and modified the formula slightly so
that I don't get the (#DIV/O!) error. I entered it like this,
=D3/(NETWORKDAYS(A3,B3)-0).

I would like to be able to pull the average hours over and have them fill in
my spreadsheet under the dates they are being divided by (start & End dates)

On Sheet2 I have all the work centers listed in column A and starting in
column C row 1, I have the dates running sequentially, starting on 3/2/09 and
running through the end of the year.

The formula would need to be able to take the available hours on a given
work center from Sheet1and split them up equally between the start and end
date (columns A & B on Sheet1). Then put those hours on the spreadsheet under
the correct work center and under the dates listed on Sheet2 row 1.

Column A Column B Column C Column D
Column E
Row1 Start Date End Date work center total hours
average hrs
row 2 3/2/09 3/6/09 18 bench 10
2

So on my spreadsheet it would show 2 hours under columns C (3/2/09) through
column G (3/6/09) it would show 2 hours for each day in the row that has work
center (18 bench). There may be multiple start and end dates that overlap
within each work center.

I hope you can help with this!

Thanks,

 

[T. Valko]

There may be multiple start and end dates that

overlap within each work center.

Well, that right there is going to be your "killer".

If you want to "chart" the hours you'd have do it separately for each
instance of a work center.

At this point what you want to do *is a lot of work*. I'm just a volunteer
(if you get my drift).