Forum Discussion
Summing (SUMIFS) with a Pivot Table and Manual Table. Easier Method?
I need assistance with making a process easier, if possible.
I take raw data from a salary file, make a Pivot Table from it to sum it, I also have manual inputs for changes to personnel that affect the forecasted persons leaving (in the example I only use current months but real summary has future months for forecast), then I have a last built table that finally sums what came from the Pivot as actuals and my forecasted (manual input/typed in J2:N9 with formulas in yellow to automate for me).
I use SUMIFS in each one but this is sort of tedious to manage (I need more lines and then I have to fix the SUMIFS sum columns or other columns, and hope to be able to pivot this data in that final third table. Is there an easier way to do? I want to be able to sum these up quickly, Pivot it so it adds up correctly.
I thought about breaking the Pivot into one tab, the manual input into another, Pivot the manual input/forecasted, then sum both Pivots into one. I can't put the manual information in the original raw data because the format's are different and I'm given the data this way.
I would suggest you spend a few minutes reading https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2010/power-query-get-transform-merge-join-append/83252b4a-785d-4e10-91dd-c887df283a65?tm=1589777580246 as nowhere in what I shared there's a merge between the 2 tables/queries, hence your questions
Assuming you used the same tables names in Excel (RawData & Inputs)
And you can select Inputs as First table and RawData as Second table, that doesn't matter in this scenario
11 Replies
With Power Query:
- RawData as one source
- Inputs as another source with appropriate transformations (Year, Month, Monthly salary...)
- Aggregation of the RawData values
- Merged with the Inputs
- Sum of Aggregated RawData values + Input values
=> As source for Pivot Table
Updated example attached
- Thank you for the response. Will try today if not tomorrow and report back.
Second question. In my example I simplified it. In my real model I have two manual tables. One does salary and second does taxes paid by company. Can it still work with using the original Pivot, my salary forecast, and then my second tax forecast manual table?
