If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sumproduct with 4 variables
I posted this last week, but didn't explain it very well, so hopefully this
is a bit clearer. One of the variables has 7 possibilites, another has 8. These can be on the worksheet if needed as a reference in the formula. Here are the 7 and 8: Emp ( in B) Emp Cat (in C) 90 10 110 11 120 12 320 13 420 14 610 15 620 17 18 From the below data: B C D E F Row Emp Emp Cat Hours Location Week 1 90 10 2.5 391 1 2 110 11 10.5 333 1 3 110 12 4.5 210 2 4 110 17 3.5 310 2 5 110 17 5.5 310 2 6 110 14 8 310 2 7 90 10 2.5 391 3 8 90 10 13 391 3 9 120 14 5 220 4 10 320 15 6 381 5 11 420 13 2 118 5 12 610 18 1.5 218 5 13 620 14 4 334 5 I would like a formula that I could drag down in the G column to produce the following, though the words are not necessary. Emp 90 in Category 10 worked 2.5 hrs in location 391 in week 1 Emp 110 in Category 11 worked 10.5 hrs in location 333 in week 1 Emp 110 in Category 12 worked 4.5 hrs in location 210 in week 2 Emp 110 in Category 17 worked a total of 9 hrs in location 310 in week 2 Emp 110 in Category 14 worked 8 hrs in location 310 in week 2 Emp 90 in Category 10 worked a total of 15.5 hrs in location 391 in week 3 Emp 120 in Category 14 worked 5 hrs in location 220 in week 4 Emp 320 in Category 15 worked 6 hrs in location 381 in week 5 Emp 420 in Category 13 worked 2 hrs in location 118 in week 5 Emp 610 in Category 18 worked 1.5 hrs in location 218 in week 5 Emp 620 in Category 14 worked 4 hrs in location 334 in week 5 The two incidents above that state 'a total of' are the 2 that have identical data in 2 of the rows ( except for the hours being summed). Rows 4/5 and rows 7/8. Much thanks, Steve |
#2
|
|||
|
|||
Sumproduct with 4 variables
|
Thread Tools | |
Display Modes | |
|
|