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

=SUMPRODUCT(($A$2:$A$22=A2)*($B$2:$B$22=B2)*($D$2: $D$22=D2)*($E$2:$E$22=E2)*$C$2:$C$22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Steve" wrote in message
...
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

Very nice. And cleaner than I was expecting. Thank you.

One more thing. I don't suppose this is possible, but I'll ask anyway.
Below are results. both the two 9s and the two 15.5s, are the result of the
same 4 variables. When that occurs, I don't suppose it would be possible to
show only one result, because they usually won't be right next to each other
as I'm showing in the sample.

2.5
10.5
4.5
9
9
8
15.5
15.5
5
6
2
1.5
4

Thank again,

Steve


"Don Guillett" wrote:

=SUMPRODUCT(($A$2:$A$22=A2)*($B$2:$B$22=B2)*($D$2: $D$22=D2)*($E$2:$E$22=E2)*$C$2:$C$22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Steve" wrote in message
...
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

.