Forum Discussion
Sum Pivot Table Results With Other Cells & Show Growth in Headcount in Another Pivot
In Q12:
=SUMIFS(Table1[[Amount ]:[Amount ]],Table1[[Department]:[Department]],$P12,Table1[[Month]:[Month]],Q$11)+SUMPRODUCT($Q$3:$AB$5,($N$3:$N$5=$P12)*($Q$2:$AB$2=Q$11))
Fill down, then to the right (or vice versa).
Note that the total in Q19 is now correct.
Jpalaci1 I believe you make it more difficult than needed. You start with raw data for monthly salaries, for the first 4 months. Then, you have yearly salaries for "newcomers" that came in during the first four months, but they are NOT in the raw data although it covers the same period.
Why not expand the raw data table with monthly data for the "newcomers" as demonstrated in the attached file. The pivot table will then automatically summarise the first 4 months correctly. With regard to head count, you may count names in stead of summing amounts. Assuming that the name is a unique identifier for employees.
Add some sub-totals to the pivot tables to get department totals whilst maintaining full detail on the employee level,
- Thank you for the reply. You’re right where I was overthinking.
