Sumproduct by Month array in a table

[Val_Gal]

Hi y'all!

I have a table named: 'TRANSACTIONS_ALL'.
I have the column 'date', formatted as mm/dd/yyyy.
I have the column "MONTH", which pulls the month of the date from the date column out and represents it as a number.
I have the column 'TYPE', classifying row items as "funds in", "funds out", "Pend Bill Out" (plus a whole bunch more indicators)
I have the column "ESTIMATE", which is the estimated value of the fund/bill coming in or out.
I have the column "Amount", which is the true value of what came in/out.
(I have about 10 other columns but I don't think its pertinent for this discussion..but if you feel I'm lacking a useful column for the equation I want, just let me know-I might have it already).

I've done so many fun things with this table! so now I'm trying to do one more fun thing. At the bottom of my table I've created my accounting forecasts to be for the day and then some other stuff.
I have a pivot table on another sheet that breaks up my expected bills due (Pend Bill Out) per month for the year, and the total estimated out is a really useful number that I need to figure out how to represent in my forecast sheet.
What I want is to be able to do some formula instead of a pivot table for my forecasting data list that just gives me this month's total of Pend Bill Out Estimate, then do another that is of the True Amount.

Previously in my forecast, I have used SumProduct to tell me Today's value, Tomorrows, etc for a 15 day forecast using '=today()+1'. the formula I used is the following:
=SUMPRODUCT(--(TRANSACTIONS_ALL[date]=TODAY()),--(TRANSACTIONS_ALL[TYPE]="Pending Bills Out"),TRANSACTIONS_ALL[ESTIMATED OUT])

MY QUESTION: for the Sum of the whole month I'm trying to obtain, I know the only part I need to change is the first array so that it could sum the whole month, but I just cannot figure out how that one portion of my equation should be formatted.

HEEEEEEEEELP....thaaaaaaanks!
Val

 

[Jonmo1]

Try

=SUMPRODUCT(--(TEXT(TRANSACTIONS_ALL[date],"mm/yyyy")=TEXT(TODAY(),"mm/yyyy")),--(TRANSACTIONS_ALL[TYPE]="Pending Bills Out"),TRANSACTIONS_ALL[ESTIMATED OUT])

 

[Val_Gal]

hmmm.. I copied and pasted into my sheet, and then replaced mm/yyyy with 08/2017. It gave me the #VALUE ! error.

I also realized it is summing funds that are positive numbers and I need it to sum only the negative numbers. any new ideas?

Val

 

[Jonmo1]

and then replaced mm/yyyy with 08/2017

Why did you do that?

The formula should literally contain the text string "mm/yyyy"

 

[Val_Gal]

let me clarify...
I tried just copying and pasting (maintaining the formula as is with the mm/yyyy), but it gave me a figure nowhere near to sum I know to be true.
So then I attempted replacing mm/yyyy with this month and year (08/2017) and that gave me the value error.

I also need it to only sum up all numbers less than 0.00.

Val

 

[Jonmo1]

You never mentioned only summing numbers less than 0

=SUMPRODUCT(--(TEXT(TRANSACTIONS_ALL[date],"mm/yyyy")=TEXT(TODAY(),"mm/yyyy")),--(TRANSACTIONS_ALL[TYPE]="Pending Bills Out"),--(TRANSACTIONS_ALL[ESTIMATED OUT]<0),TRANSACTIONS_ALL[ESTIMATED OUT])