# SUMPRODUCT WITH ADDITION at End

#### davidhall80

##### Well-known Member
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### jimrward

##### Well-known Member
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
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
or add a help column which sums the hours together if you are certian the rates will never change for regular and OT hours

##### Well-known Member
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
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
see if this works

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

Replies
0
Views
418
Replies
3
Views
240
Replies
2
Views
926
Replies
3
Views
188
Replies
3
Views
1K

1,171,686
Messages
5,876,897
Members
433,218
Latest member
JReeves24

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

### Which adblocker are you using?

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

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