Assistance making Pivot table sums be a formula instead of Pivot Table

[Val_Gal]

Hi guys,

(I apologize ahead of time for over-explaining! I just don't know how else to explain what i'm looking at and doing)

I have an accounting forecast that consists of a large table of transactions + expected transactions, which is then set up into a pivot table.
My pivot table shows per day what bills have been paid and what are still due,
Now my boss needs it so that I take the Sum Total of each date on my pivot table to make a different forecasting chart on the side.

**MY QUESTION: How do I translate the sums generated by the pivot table to be a formula in a cell?
The main problem is that my pivot table is FILTERING OUT some of the transaction items (TYPE of Bill, Column C) on my main table so that it not indicating bills or fund transfers that are not essential to my boss.

HOW MY DATA IS SET UP:
Data source of pivot table of bills still due daily: TABLE NAME: Transactions_ALL
* This table is all transactions occurring in our bank account PLUS forecast of all expected bills for the year. Example: Rent PAID on 12/1 is
--Column A: Date
--Column B: Description of Bill (ex: Comcast, Rent, GA Power)
--Column C: Type of Bill (example, "Pending Bill Out", "Received Invoice", "Transfer to Savings", "Funds Paid") - I currently have about 10 labels of types
--Column D: Remaining Due ($$)
--Column E: Sum Paid

Example:
A: 12/1/17 B: RENT C:Funds Paid D:$0.00 E:$1000
B: 1/1/18 B: RENT C:Pending Bill Out D:$1000.00 E:$0

My Pivot table puts this data in a nice setup. Its
Report Filters: Year, Month, TYPE, showing only Pending Bills Out, Past Due, Payroll
Columns: Values
Rows: Date, Description
Values: Remaining Due
EXAMPLE:
12/20/2017 REMAINING DUE
--Comcast $100
--GA Power $50
-TOTAL $150.00
12/21/17
--Gas Bill $50
--Car Service $200
-Total $250.00


WHAT I NEED IN THE FORMULA EQUATION: I'm trying to make a formula equation that sums up the Remaining Due(TOTAL by Date) with the exact filters I have of the TYPE filter in my pivot table.
I think I need to use a "SumProduct" formula, but I can't figure out how to have the criteria sum multiple criterias.

I've gotten THIS FAR : =SUMPRODUCT(--(TRANSACTIONS_ALL[date]=TODAY()),--(TRANSACTIONS_ALL[TYPE]="Pending Bills Out"),TRANSACTIONS_ALL[REMAINING DUE])
--The result is correct in summing the remaining due of all expected or paid transactions TODAY, labelled as TYPE "Pending Bills Out".
--QUESTION: How do I get it to sum multiple types: Pending Bills Out + Past Due + Payroll




Anything you can do to help is appreciated!!
Val

 

[AFPathfinder]

First, thanks for posting all that information. It helps to visualize the setup.

For a formula instead of adding it to the pivot table, are you sure a SUMIFS wouldn't suffice? You can limit by date and exclude certain criteria (static) by using "<>Fund Transfer" or include specific criteria with "=Pending Bills Out".

=IF(A4>0,SUMIFS(TRANSACTIONS_ALL[REMAINING DUE],TRANSACTIONS_ALL[date],A4,
                                                TRANSACTIONS_ALL[TYPE],"<>Fund Transfer",
                                                TRANSACTIONS_ALL[TYPE],"<>Stock Fund Account"),"")

However, if you need it to be variable to changes in the pivot table filters, it would probably be best to do a calculated field inside the pivot table.

 

[Val_Gal]

Thanks so much for your suggestions!
I am self-taught with excel and am quite the amateur, so it never occurred to me that there was the option of doing calculations within a pivot table.
I will tell you... there is so much more going into this financial chart than this one pivot table, and now that you mentioned the option of calculations within the pivot table, I believe this could be simplified. If you don't mind assisting a little more... I will explain the additional and maybe you can help clean up my financial chart.

Within my Transactions All Table, I indicated I pull in all data from the bank (daily), as well as estimate all funds expected to go out each day from now until January 2019.
DATA SETUP INFO:
Types:
* Funds In
* Funds Out
* Bank Pending In (transactions not yet settled in the bank but do show funds pending to come in as of the time I check accounting in the morning)
* Bank Pending Out
* Pending Checks Cut (all checks we've send out but have not cleared)
* Pending Payroll Check (we don't have direct deposit, so cut payroll checks not yet clear)
* Earmarked for Savings
---(a false transaction - a value based on prev. day's deposit that we hope to transfer to savings. ex: if dep. was $1,000 on 12/20, I enter an $100 to go to savings. its not actually coming in or going out yet, its not indicated in the 'remaining due' calculation)

FINANCIAL CHART SET UP
On the same excel sheet of the Pivot Table, I have a little chart of math:
* $100,000......YESTERDAY'S Bank Balance
- $ 5,000......Minus Pending Checks Cut
- $ 7,000......Minus Outstanding Payroll Checks Cut
- $ 1,000......Minus Funds Earmarked for Savings
=$ 87,000.......EQUALS TODAY'S SPENDABLE MONEY


What the Boss Man wants (and I can't figure out how to do) is to have a calculation dependent on the pivot table and sit exactly in the pivot table.
He wants the Pivot Table to do this: he wants it to take the total of Today's Spendable Money, and make a calculation of
*Spendable + Estimated Deposit - Remaining Due = True Available Funds
He visually wants the calculation to line up with the pivot table, something like this:

FINANCIAL CHART SET UP
On the same excel sheet of the Pivot Table, I have a little chart of math:
* $100,000......YESTERDAY'S Bank Balance
- $ 5,000......Minus Pending Checks Cut
- $ 7,000......Minus Outstanding Payroll Checks Cut
- $ 1,000......Minus Funds Earmarked for Savings
=$ 87,000.......EQUALS TODAY'S SPENDABLE MONEY

PIVOT TABLE-------------------------------------------------------------------------------
COLUMN A.........|....Column B...............|....Column C......................|...Column D
12/20/2017...|... REMAINING DUE...|...ESTIMATED DEPOSIT...|...True Available Funds
--Comcast.......|........... $100
--GA Power.....|............. $50
-TOTAL............|........... $150.00........|.............$10,000..............|...$96,850 (=Today's Spendable + Est. Dep - Remaining Due)
12/21/17
--Gas Bill ........|...........$50...............|
--Car Service...|........ $200...............|
-Total .............|.........$250.00..........|..............$5,000................|...$101,600 (=Yesterdays True Val. Funds (96850) + Est. Dep - Remaining Due)


Originally I was going to try to turn the pivot table data to be a chart equation instead, but Boss Man specifically wants it to visually be set up like this.
Is this do-able with pivot table calculations? I'm reading up on pivot table calculations now, so I will be fiddling with that today (I'm a quick study).
I feel the difficult part would be combining the chart data with the pivot table data.

I would love to know what you think!! if this is more assistance than you were planning to give, I would completely understand. You have already triggered a good start with the mention of pivot table calculations and it is much appreciated!
Thanks, Val

 

[AFPathfinder]

Where does the table get it's initial available funds? Your calculated field would be just "Est. Dep - Remaining Due" and then you set the field to a running total:
Value Field Settings → Show Values As tab → "Running Total In" in the combobox → select "Date" in inputbox → OK

Having it skip the descriptions and just display on the dates might take a little more research.

 

[Val_Gal]

The initial available funds comes from the [Transactions_ALL] table of all the data from the bank and estimated bills out.

So, the chart (little table) I indicated before is:

Bank Balance =SUM(Transactions_ALL[PAID])

minus Pending Checks to be clear =SUMPRODUCT(--(TRANSACTIONS_ALL[date]<=TODAY()),--(TRANSACTIONS_ALL[Type]="PENDING CHECK"),TRANSACTIONS_ALL[AMOUT DUE])

Minus Outstanding Payroll Checks Cut =SUMPRODUCT(--(TRANSACTIONS_ALL[date]<=TODAY()),--(TRANSACTIONS_ALL[Type]="PEND PAYROLL CHECK"),TRANSACTIONS_ALL[AMOUT DUE])

Minus Funds earmarked for savings =SUMPRODUCT(--(TRANSACTIONS_ALL[date]<=TODAY()),--(TRANSACTIONS_ALL[Type]="EARMARKED SAVED"),TRANSACTIONS_ALL[ESTIMATED OUT])

EQUALS TODAY's SPENDABLE MONEY. from Here, this total, my boss wants me to ADD estimated Deposit, then SUBTRACT the sum of the remaining due.

* $100,000......YESTERDAY'S Bank Balance
- $ 5,000......Minus Pending Checks Cut
- $ 7,000......Minus Outstanding Payroll Checks Cut
- $ 1,000......Minus Funds Earmarked for Savings
=$ 87,000.......EQUALS TODAY'S SPENDABLE MONEY


I think I gone way over my own head on this endeavor