SUMPRODUCT multiple conditions
 

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

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