Help needed to create a pivot table..

I am new to Pivot tables and I have watched an online video and it "looks" easy but I am struggling to get one to work for the attached file.

I am concerned that maybe what I am trying to do is too complex, meaning, that a Pivot table cannot work on this size of a table?

My goal is to be able to do two things.

a) Calculate monthly revenues and costs relating to these specific staff, by person name and by Position, eg Partners, Counsel, Associates etc. for each cost item, eg monthly salary, health insurance, taxes etc

b) the attached assumes some staff may join in the future, can a pivot table work this way? That i can add in names and new staff terms and will the pivot table auto update?

Thanks for advice on how best to structure this for pivots to work and make it easier to process the data quickly rather than manual excel?

Hi Carina

1) I noticed that you have an additional column in the middle (Column B). Once I removed that, I'm able to insert a pivot table for the data. Usually, I'm working on much bigger data than yours. So, I think your data is fine. I would suggest you to create a sample worksheet to show what kind of data you have and manually enter the desire results on another sheet. It would be easier for others to assist you with a solution.

2) I think you can use the Table function. Just select all the data then press Ctrl + T (I did on your file). Whenever you add new comer below the last staff, it will auto extended the table further down.

Refer attached for your easy understanding

Originally Posted by Hikari

Hi Carina

1) I noticed that you have an additional column in the middle (Column B). Once I removed that, I'm able to insert a pivot table for the data. Usually, I'm working on much bigger data than yours. So, I think your data is fine. I would suggest you to create a sample worksheet to show what kind of data you have and manually enter the desire results on another sheet. It would be easier for others to assist you with a solution.

2) I think you can use the Table function. Just select all the data then press Ctrl + T (I did on your file). Whenever you add new comer below the last staff, it will auto extended the table further down.

Refer attached for your easy understanding

Ah, thank you for seeing that extra Column. I thought I was going crazy as I couldnt understand what was wrong ! So I did what you suggested and I attach the "ideal output" I am trying to achieve quickly and wonder is a Pivot table going to be able to generate this for me? What I am unclear about is where for example there is complexity of someone starting mid month etc, how is all of that calculated? So for each section of outputs I include what I think needs calculating by the Pivot table for that section but I am not sure how to go about generating all of this. As you can see, I am effectively generating monthly data, summarised by Position so I can then do a high level P&L.

Thank you JeteMc for taking the time to help me with this.

I am unable to get the Income Tax one to work? In your example workbook, the income tax was 0% so I just went and inputted 10% to see it flow through, and nothing changed. Can I check, when I update the inputs in the table, should they auto flow through or do I need to do something? I am not familiar working with tables hence why I am asking? Also, will this Table work if I delete a row or insert a row (to remove or add new employees)?

thanks. That solved the income tax and it is great to know I can insert and delete rows.

Can I ask assistance with another problem?

I want to do the formula for the Annual Bonus payment (Bonus is calculated as Bonus % of Annual salary). I want to say "IF the current month equals Bonus Month" THEN do the sum of product... but I cannot get that bit around looking up the Bonus Month to work. I tried this formula but it is not working:

=SUMPRODUCT((Position=$A47),MONTH(D$9)=MONTH(Bonus_Month),(SALARY*BONUS))

I have now DEFINED the arrays for columns like Salary and Bonus and Bonus Month.

The Bonus month in the table is a written month December. As I want the model to work over multi years, I cannot have an actual date format eg 12/2021 in that cell or else it will not work. So i need a formula to say "the month of december every year THEN look up and pay a bonus as per the Bonus Column etc.".

Originally Posted by JeteMc

Since the bonus month will always be December I would use: =IF(MONTH(D$9)=12,SUMPRODUCT((Position=$A47)*(SALARY)*(BONUS)),"")
If that doesn't work please upload a sample of the file with the defined names applied.
Let us know if you have any questions.

That worked.Thank you!.