SUMPRODUCT multiple conditions

[jwfullerton]

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

 

[RagDyer]

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.