kramerica814
New Member
- Joined
- Jul 1, 2007
- Messages
- 3
I couldn't find a similar solution, so I apologize if something like this has already been posted. In a nutshell, I'm trying to multiply a payroll tax rate that varies by country by salaries in an employee roster (employees tagged with country and department) and summarize the result by department.
Specifically, I have an assumptions table that has a payroll tax assumption by country and month
This employee roster has salaries by month for hundreds of employees in various department/country combos.
The output I'm looking for is simply payroll tax (salary*PT %) by department and month. So, for example, 510000-Executive would total $3,643 in January ($37,500 of US comp at 8.5% and $8,927 of Canadian comp at 5.1%)
Is there a way to do this with a formula? Seems like a potential job for sumproduct, but I can't get it to work across multiple ranges. My alternative would be to calculate the PT by employee to to right and then sum, but there are a lot of other metrics that use the similar logic, so I couldn't do that for everything.
Thanks!
Specifically, I have an assumptions table that has a payroll tax assumption by country and month
| PT Calc Example.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Jan-21 | Feb-21 | Mar-21 | |||||
| 2 | Country | Currency | Metric | |||||
| 3 | CAN | CAD | PT % | 5.1% | 5.1% | 5.1% | ||
| 4 | MEX | MXN | PT % | 3.0% | 3.0% | 3.0% | ||
| 5 | CR | COP | PT % | 26.5% | 26.5% | 26.5% | ||
| 6 | POR | EUR | PT % | 0.0% | 0.0% | 0.0% | ||
| 7 | GER | EUR | PT % | 0.0% | 0.0% | 0.0% | ||
| 8 | ISR | ILS | PT % | 0.0% | 0.0% | 0.0% | ||
| 9 | US | USD | PT % | 8.5% | 8.0% | 7.5% | ||
Expense Assumptions | ||||||||
This employee roster has salaries by month for hundreds of employees in various department/country combos.
| PT Calc Example.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 11 | Dept (ADP) | Country | Name | Wage (LC) | CURR | Start Date | End Date | Dec-20 | Jan-21 | Feb-21 | Mar-21 | ||
| 12 | 510000-Executive | US | Employee 1 | 225,000 | USD | 5/25/2017 | 18,750 | 18,750 | 18,750 | 18,750 | |||
| 13 | 510000-Executive | US | Employee 2 | 225,000 | USD | 6/16/2017 | 18,750 | 18,750 | 18,750 | 18,750 | |||
| 14 | 510000-Executive | CAN | Employee 3 | 133,900 | CAD | 1/13/2020 | 11,158 | 8,927 | 8,927 | 8,927 | |||
| 15 | 520000-Finance | US | Employee 4 | 95,000 | USD | 12/2/2019 | 7,917 | 7,917 | 7,917 | 7,917 | |||
| 16 | 520000-Finance | US | Employee 5 | 115,000 | USD | 10/20/2020 | 9,583 | 9,583 | 9,583 | 9,583 | |||
| 17 | 530000-People | US | Employee 10 | 95,000 | USD | 8/19/2019 | 7,917 | 7,917 | 7,917 | 7,917 | |||
| 18 | 530000-People | US | Employee 11 | 160,000 | USD | 10/20/2020 | 13,333 | 13,333 | 13,333 | 13,333 | |||
| 19 | 530000-People | CAN | Employee 12 | 115,000 | CAD | 12/15/2020 | 9,583 | 7,667 | 7,667 | 7,667 | |||
| 20 | 530000-People | CAN | Employee 13 | 60,000 | CAD | 4/20/2021 | - | - | - | - | |||
| 21 | 530000-People | MEX | Employee 14 | 3,500,002 | MXN | 7/20/2021 | - | - | - | - | |||
| 22 | 530000-People | US | Employee 15 | 200,000 | USD | 7/26/2021 | - | - | - | - | |||
| 23 | 530000-People | US | Employee 16 | 275,000 | USD | 7/26/2021 | - | - | - | - | |||
| 24 | 530000-People | US | Employee 17 | 170,000 | USD | 8/3/2021 | - | - | - | - | |||
| 25 | 540000-Operations | US | Employee 21 | 107,986 | USD | 12/2/2019 | 8,999 | 8,999 | 8,999 | 8,999 | |||
| 26 | 540000-Operations | US | Employee 22 | 112,000 | USD | 6/15/2021 | - | - | - | - | |||
Headcount Roster | |||||||||||||
The output I'm looking for is simply payroll tax (salary*PT %) by department and month. So, for example, 510000-Executive would total $3,643 in January ($37,500 of US comp at 8.5% and $8,927 of Canadian comp at 5.1%)
| PT Calc Example.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| F | G | H | I | J | K | |||
| 121 | PT by Department | |||||||
| 122 | 510000-Executive | |||||||
| 123 | 520000-Finance | |||||||
| 124 | 530000-People | |||||||
| 125 | 540000-Operations | |||||||
| 126 | 550000-Information Security | |||||||
| 127 | 610000-Machine Learning | |||||||
| 128 | 620000-Software Engineering | |||||||
Headcount Roster | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F122:F128 | F122 | =SORT(UNIQUE($A$12:$A$73)) |
| Dynamic array formulas. | ||
Is there a way to do this with a formula? Seems like a potential job for sumproduct, but I can't get it to work across multiple ranges. My alternative would be to calculate the PT by employee to to right and then sum, but there are a lot of other metrics that use the similar logic, so I couldn't do that for everything.
Thanks!
