Split Salary Report Formulas

bestschnauzermom

New Member
Joined
Aug 9, 2019
Messages
5
I'm building a fairly complicated spreadsheet that is going to be updated weekly. The spreadsheets name changes monthly so building a macro wouldn't be user friendly and I'm trying to build formulas with the workbook. The user imports data from another spreadsheet into "Week1". From there the data is pulled into the "Total" tab with the following formula.

Week 1 tab, column B is the employee, column E is their pay for the week and B8 is the employee name then multiplying by % of week.
=SUMIF(Week1!$B:$B,$B8,Week1!$E:$E)*$F6


After this information pulls to the Total tab I have another formula that splits the employee between two companies by department either 50% to 100% with this formula:

=IF(A11="K022","0%",IF(A11="K021","0%",IF(A11="K023","0%",IF(A11="C021/K021","50%",IF(A11="C022/K022","50%",IF(A11="C023/K023","50%","100%"))))))
=IF(D11="100%","0%",IF(D11="50%","50%",IF(D11="0%","100%")))


Then I have to take these and move them into the perspective companies / departments on three tabs. "Manager", "Clerical" or "Other".

=(SUMIFS(Total!$F:$F,Total!$B:$B,$C8,Total!$C:$C,"Manager")
=SUMIFS(Total!F:F,Total!$B:$B,$B8,Total!$C:$C,"Clerical")
=(SUMIFS(Total!F:F,Total!$B:$B,$B6,Total!$C:$C,"Other")


<colgroup><col></colgroup><tbody>
</tbody>
Here is the problem.

Data imports great
Data pulls to the total tab and breaks out percentages great
I cannot get the data WITH PERCENTAGES to pull to the department tabs.

I hope I explained this correctly, if not let me know.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top