Sum Pivot Table Results With Other Cells & Show Growth in Headcount in Another Pivot

[Jpalaci1]

I have raw data that I summarize in a Pivot Table. I then have a table-like area for inputs for new persons coming in (peach area for inputs and formulas for values).

1) I currently use a SUMIFS (example uses SUMIF but real data uses multiple criteria. to add appropriate department with possible inputted forecast value to sum a total. Is there a way to add these in an easier way? I currently am going through the painstaking process of formatting/moving the sum range and criteria ranges around. How can I sum this Pivot Table and input table together easier? Should I make a second Pivot and attempt to sum the two Pivots that way?

2) I also want to use a Pivot on the input table to show discrete values of when the count of people grows. I have it showing the salary when the person starts but not sure how to make it show a 1 when the person starts.

[#2 · Accepted solution]

@Jpalaci1 

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.

[#3]

@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,

[#4]

Thank you for the reply. You’re right where I was overthinking.