Help with a monthly percentage formula

[Schubby10]

Hey guys, So I have a list of total monthly salary for each person. The trouble is each person is split among 16 jobs with various percentages. Is there a simple formula to sum all of the salary devoted to each job without building out monthly calculations for each project?

My data is arranged as cell A3-L97 is the monthly salaries then columns N3-AD97 is the percentage spent on a particular job. All I am trying is a formula to calculate salary based on each job per month.

Any ideas or do I need to build it out?

 

[CyrusTheVirus]

can you copy/paste a sample of your data so we can see it? if it's confidential info then just change the names etc.

 

[joeu2004]

My data is arranged as cell A3-L97 is the monthly salaries then columns N3-AD97 is the percentage spent on a particular job. All I am trying is a formula to calculate salary based on each job per month. Any ideas or do I need to build it out?

I assume that columns N:AD represent the jobs. FYI, that is 17 jobs, not 16. I assume that columns A:L represent the months. And rows 3:97 represent the employees (95).

So one design might be: enter the job names into N99:AD99 and the month names into M100:M111. Then enter the following formula into N100 and copy N100 into N100:AD111:

=SUMPRODUCT(INDEX($A$4:$L$97, 0, ROWS(M$100:M100)), N$4:N$97)

The result might look something like this, representing only 5 employees (rows 4:8):


[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]AC[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]AE[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]Nov[/TD]
[TD="align: right"]Dec[/TD]
[TD="align: right"]TOTAL[/TD]
[TD="align: right"]Job1[/TD]
[TD="align: right"]Job2[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]Job16[/TD]
[TD="align: right"]Job17[/TD]
[TD="align: right"]TOTAL[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]$1,500[/TD]
[TD="align: right"]$500[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$1,200[/TD]
[TD="align: right"]$12,200[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]$900[/TD]
[TD="align: right"]$900[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]$1,200[/TD]
[TD="align: right"]$1,500[/TD]
[TD="align: right"]$11,200[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]$1,400[/TD]
[TD="align: right"]$1,400[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]$800[/TD]
[TD="align: right"]$1,100[/TD]
[TD="align: right"]$12,600[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]$1,300[/TD]
[TD="align: right"]$1,000[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$1,400[/TD]
[TD="align: right"]$12,200[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]$1,100[/TD]
[TD="align: right"]$500[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]$600[/TD]
[TD="align: right"]$1,100[/TD]
[TD="align: right"]$10,900[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[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: right"]$59,100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]

---

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

---

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

---

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

---

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

---

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

---

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

---

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

---

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

---

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

---

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

---

[/TD]
[/TR]
[TR]
[TD="align: center"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Job1[/TD]
[TD="align: right"]Job2[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]Job16[/TD]
[TD="align: right"]Job17[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Jan[/TD]
[TD="align: right"]$184[/TD]
[TD="align: right"]$299[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]$381[/TD]
[TD="align: right"]$499[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]101[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]$157[/TD]
[TD="align: right"]$213[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]$234[/TD]
[TD="align: right"]$312[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]110[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Nov[/TD]
[TD="align: right"]$123[/TD]
[TD="align: right"]$176[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]$209[/TD]
[TD="align: right"]$325[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]111[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Dec[/TD]
[TD="align: right"]$192[/TD]
[TD="align: right"]$298[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]$361[/TD]
[TD="align: right"]$518[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]112[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TOTAL[/TD]
[TD="align: right"]$1,794[/TD]
[TD="align: right"]$2,837[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]$3,527[/TD]
[TD="align: right"]$4,785[/TD]
[TD="align: right"]$59,100[/TD]
[/TR]
</tbody>[/TABLE]