SUMPRODUCT WITH ADDITION at End

[davidhall80]

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

 

[jimrward]

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]

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]

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]

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]

Hi,

Is this what you're looking for?

Book3
	A	B	C	D	E
1	Zone	Status	Pay Rate	REG Hours Worked	OT Hours Worked
2	9	Old	0.35	49	34
3	5	Old	0.35	27	37
4	7	Old	0.22	50	33
5	4	Old	0.19	48	18
6	1	Old	0.34	15	20
7	4	Change	0.27	32	37
8	2	New	0.22	48	31
9	4	Change	0.18	15	22
10	6	New	0.4	15	40
11	2	New	0.11	16	24
12	3	New	0.1	35	27
13	5	Change	0.16	38	32
14	7	Old	0.25	45	34
15	1	New	0.13	23	40
16
17
18		21.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]

see if this works

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