Needed: A formula to calculate the product of a multi-dimensional range of values...WITHIN A SINGLE CELL

[pdx2dca]

I’m pretty sure that manipulation of the SUMPRODUCT function will solve my problem…but I can’t figure out what that manipulation is. So here’s my question:


Is there a way to calculate – within a single cell – a multi-dimensional (i.e. multiple criteria) product of a range of values when the inputs for the criteria exist on a separate table?


For example, let’s say I want to calculate the total cost of both the regular and overtime hours worked by a group of employees. The employees’ hours over a three-day period are shown in the last three columns of the table below (Day1, Day2, and Day3):

Employee	Wage Type	Day1	Day2	Day3
Bill	Regular	8 hours	6 hours	8 hours
Bill	Overtime	2 hours		1 hours
Mary	Regular	7 hours	8 hours	8 hours
Mary	Overtime		2 hours	2 hours
Kathryn	Regular	10 hours	10 hours	10 hours
John	Regular	6 hours	8 hours	4 hours
John	Overtime		1 hours

<tbody>
</tbody>

However, the regular and overtime wages for the employees are in a separate table, below:

Employee	Regular	Overtime
Kathryn	$35.00 / hour	$35.00 / hour
Mary	$20.00 / hour	$30.00 / hour
John	$25.00 / hour	$37.50 / hour
Bill	$30.00 / hour	$45.00 / hour

<tbody>
</tbody>

Is there a formula that will calculate – within a single cell – the total cost of all hours worked per employee wage and wage type? (Hint: The answer is $2,912.50)

FYI: I can re-sort and/or reorganize the employee wage table (the 2nd table above) if I need to. I can’t, however, do anything about the layout of the ‘Hours Worked’ table.

 

[DRSteele]

I think this might help. Copy D11 down for each workman.

	A	B	C	D	E
1	Employee	Wage Type	Day1	Day2	Day3
2	Bill	Regular	8.00	6.00	8.00
3	Bill	Overtime	2.00		1.00
4	Mary	Regular	7.00	8.00	8.00
5	Mary	Overtime		2.00	2.00
6	Kathryn	Regular	10.00	10.00	10.00
7	John	Regular	6.00	8.00	4.00
8	John	Overtime		1.00
9
10	Employee	Regular	Overtime	Cost
11	Kathryn	35.00	35.00	1050.00
12	Mary	20.00	30.00	580.00
13	John	25.00	37.50	487.50
14	Bill	30.00	45.00	795.00
15				2912.50

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet36​

Worksheet Formulas Cell Formula D11 =SUMPRODUCT(($A11=$A$2:$A$8)*($B11*(B$10=$B$2:$B$8))*$C$2:$E$8)+SUMPRODUCT(($A11=$A$2:$A$8)*($C11*(C$10=$B$2:$B$8))*$C$2:$E$8) D15 =SUM(D11:D14) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[barry houdini]

Assuming first table in A1:E8 unchanged and second table in H1:J5 with only numbers, e.g. 35.00 formatted as currency to display $35.00 then you can use this formula

=SUMPRODUCT((0&SUBSTITUTE(C2:E8," hours",""))*(SUMIF(H2:H5,A2:A8,I2:I5)*(B2:B8="Regular")+SUMIF(H2:H5,A2:A8,J2:J5)*(B2:B8="Overtime")))

I get $2,915.50 as you suggest

 

[pdx2dca]

Unfortunately, that solution requires 5 cells (D11 through D14, with those summed in D15).

I need a formula within a single cell that will calculate the $2,915.50.

 

[pdx2dca]

That did it, Barry. Thanks.

In my actual 'hours worked' table, the " hours" suffix is a number format. So, I was able to do the calc with the following:

=SUMPRODUCT((C2:E8)*(SUMIF(H2:H5,A2:A8,I2:I5)*(B2:B8=I1)+SUMIF(H2:H5,A2:A8,J2:J5)*(B2:B8=J1)))

Thanks again.