Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct - multiple conditions | Excel Worksheet Functions | |||
SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
Sumproduct Multiple < Conditions | Excel Worksheet Functions | |||
Multiple SumProduct conditions | Excel Worksheet Functions | |||
Sumproduct Multiple Conditions | Excel Worksheet Functions |