SUMPRODUCT WITH ADDITION at End

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
Same as before but with addition

Column A Has Zone 1 through Zone 9. Column B has "New", "Change", or "Old". Column C has Pay Rate. Column D has REG hours worked. Column E has OT Hours worked. OT and REG pay is the same, so I would like to get total Gross Pay for "Zone 2" "New" assignments. If column A = Zone 2, and Column B = New, than multiply C with the sum of D and E add all those products together. The difference here is D and E have to be added together before multiplying to C..............
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,791
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
keeping it simple can you not add them together post calculation by breaking it into 2 parts, regular pay and OT pay

[zone2 regularhours*rate] + [zone2 overtimehours*rate]

this way you can allow for different rates should this be the case in the future
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
David

Same as what before?:)

If this is a continuation of another thread it might be helpful if you posted a link to the original.
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,791
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
or add a help column which sums the hours together if you are certian the rates will never change for regular and OT hours
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows

ADVERTISEMENT

Hello,

DOn't start new threads, just carry on the original, if it is the same/continuing the question

=SUMPRODUCT(--($A$1:$A$6=F1),--($B$1:$B$6=G1),($C$1:$C$6)*($D$1:$D$6+$E$1:$E$6))

where F1 =2 and G1 = new
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hi,

Is this what you're looking for?
Book3
ABCDE
1ZoneStatusPay RateREG Hours WorkedOT Hours Worked
29Old0.354934
35Old0.352737
47Old0.225033
54Old0.194818
61Old0.341520
74Change0.273237
82New0.224831
94Change0.181522
106New0.41540
112New0.111624
123New0.13527
135Change0.163832
147Old0.254534
151New0.132340
16
17
1821.78
Sheet1


formula is =SUM(IF(($A$2:$A$15=2)*($B$2:$B$15="New"),$D$2:$E$15*$C$2:$C$15))
confirmed with ctrl+shift+enter
 

TheNoocH

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
see if this works

=SUMPRODUCT(--(A1:A6="Zone 2"),--(B1:B6="new"),(C1:C6),(D1:D6+E1:E6))
 

Forum statistics

Threads
1,137,207
Messages
5,680,194
Members
419,888
Latest member
Prasad K

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top