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):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Wage Type[/TD]
[TD="align: right"]Day1[/TD]
[TD="align: right"]Day2[/TD]
[TD="align: right"]Day3[/TD]
[/TR]
[TR]
[TD]Bill
[/TD]
[TD]Regular[/TD]
[TD="align: right"]8 hours[/TD]
[TD="align: right"]6 hours[/TD]
[TD="align: right"]8 hours[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Overtime[/TD]
[TD="align: right"]2 hours[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1 hours[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Regular[/TD]
[TD="align: right"]7 hours[/TD]
[TD="align: right"]8 hours[/TD]
[TD="align: right"]8 hours[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Overtime[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2 hours[/TD]
[TD="align: right"]2 hours[/TD]
[/TR]
[TR]
[TD]Kathryn[/TD]
[TD]Regular[/TD]
[TD="align: right"]10 hours[/TD]
[TD="align: right"]10 hours[/TD]
[TD="align: right"]10 hours[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Regular[/TD]
[TD="align: right"]6 hours[/TD]
[TD="align: right"]8 hours[/TD]
[TD="align: right"]4 hours[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Overtime[/TD]
[TD][/TD]
[TD="align: right"]1 hours[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Employee[/TD]
[TD="align: right"]Regular[/TD]
[TD="align: right"]Overtime[/TD]
[/TR]
[TR]
[TD]Kathryn[/TD]
[TD="align: right"]$35.00 / hour[/TD]
[TD="align: right"]$35.00 / hour[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]$20.00 / hour[/TD]
[TD="align: right"]$30.00 / hour[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]$25.00 / hour[/TD]
[TD="align: right"]$37.50 / hour[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD="align: right"]$30.00 / hour[/TD]
[TD="align: right"]$45.00 / hour[/TD]
[/TR]
</tbody>[/TABLE]

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
Bill	Regular
Bill	Overtime
Mary	Regular
Mary	Overtime
Kathryn	Regular
John	Regular
John	Overtime
Kathryn
Mary
John
Bill

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D9D9D9"]Employee[/TD]
[TD="bgcolor: #D9D9D9"]Wage Type[/TD]
[TD="bgcolor: #D9D9D9"]Day1[/TD]
[TD="bgcolor: #D9D9D9"]Day2[/TD]
[TD="bgcolor: #D9D9D9"]Day3[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8.00[/TD]
[TD="align: right"]6.00[/TD]
[TD="align: right"]8.00[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]8.00[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]2.00[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10.00[/TD]
[TD="align: right"]10.00[/TD]
[TD="align: right"]10.00[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]4.00[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #D9D9D9"]Employee[/TD]
[TD="bgcolor: #D9D9D9"]Regular[/TD]
[TD="bgcolor: #D9D9D9"]Overtime[/TD]
[TD="bgcolor: #D9D9D9"]Cost[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]35.00[/TD]
[TD="align: right"]35.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]1050.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]20.00[/TD]
[TD="align: right"]30.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]580.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]25.00[/TD]
[TD="align: right"]37.50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]487.50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]30.00[/TD]
[TD="align: right"]45.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]795.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2912.50[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet36​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D11[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D15[/TH]
[TD="align: left"]=SUM(D11:D14)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.