SUMPRODUCT multiple conditions
Try this in D5 of the Summary sheet, and copy down as needed:
=SUMPRODUCT((Hours!B$10:B$145=B5)*(Hours!$F$9:$J$9 =Hours!$D$6)*Hours!$F$10:$J$145)
I believe I followed your description accurately.
Was confused as to why you used a cell in the Hours sheet to designate the
month criteria, instead of placing it in the Summary sheet, where I believe
it should logically go.
Also, I was wondering why you had no suggestions for this relatively easy
question, and then I realized that most people around here prefer the unary
form of the Sumproduct function, which just *doesn't calculate* with this
scenario of uneven range sizes using columns and rows, as the asterisk form
does.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"jwfullerton" wrote in message
...
I want to be able to return a value based on mulptile criteria (from both
columns and rows).
Worksheet 1 (Summary):
I have Location, Postition, Rate, Hours for Date, Dollars for Date as
headers in Coulmns A-E, Row 4 respectively.
Location, Position, Rate have data in Rows 5-63
Dollars for Date is formulated to take the product of Rate * Hours for
Date
Worksheet 2 (Hours):
I have Location, Position, Position Number, Person, Rate, Oct, Nov, Dec,
Jan, Feb in Colums A-J, Row 9 respectively.
In Cell D6 I have the current report period month.
For each month (Columns F-J) I will enter the number of hours each person
works in a particular position (Data in rows 10-145). Multiple people
work
within the same position.
I need a formula in the 'Hours for Date' column on the Summary Worksheet
based on a specified month from the Hours worksheet, summed together by
'Position'. I want the number of hours worked by position per month on
the
summary worksheet. I want the formula to automatically update based on
what
Date I put into Cell D6 on the Hours worksheet.
Please let me know if you need any clarification.
Thanks.
Jesse
|