Sumproduct multi criteria excel 2016 - Monthly Cash Flow Forecast spreadsheet

[F0RE5T]

Hi

Many thanks in advance for any help and I hope the info below is clear. If not just ask.

I am creating a monthly cash flow workbook containing sheets for various purposes.

One Sheet is called “CashFlow Mth”; which I assume most know how a monthly cashflow for a year looks like.
Column A (A19:A31) are row “Expenditure Type” headings e.g. Salary, Insurance, Marketing etc E.g. $A19 would collate “salary”, $A20 is utilities.

Columns on Row 3 (B3:M3) are the months headings so each column will show a total expenditure figure for that month in-line with the appropriate Col A heading.

Individual expenditure records are listed in sheet called “Outgoings”. On this sheet each row (A4:A303) records of a single record of expenditure.

The sheets Column F2:Q2 are headings that match the sheet “Cash Flow Mth” Column A headings

Below is a formula I am trying to work on. The cell references are the actual ranges. Obviously, I want to copy the formula across the “CashFlow Mth”;cells B19:M31 so is dynamically updated.

I know the “Month” element of the formula works to find the appropriate month transactions but do not know how to change it therefore sum only the figures under the correct “column” and on the relevant “Row” for the appropriate month

In brief, I want to sum all the transactions figures for each “Expenditure Type” for each month.

SUMPRODUCT((MONTH(Outgoings!$B$4:$B$303)=MONTH(1&F$3)+(Outgoings!F$2:Q$2=$A19))*(Outgoings!F$4:Q$303))

Any help many thanks

 

[BlueHornet]

I'm not quite following your formula, so I won't address that directly. I think I understand what you're after, so after some clarifications to your data that I expect, the formula below should work.

Basically the "Outgoings" sheet as I understand it is a journal. It will contain columns for "Amount", "Account", and "Date". It will be helpful if it also contains another column to calculate the Date field into an "End of Month" value. So for each of the rows with values in columns A, B, C, the D value =EOMONTH( C4, 0)

The "CashFlow Mth" sheet headers for the months at B3:M3 should be your month-ending dates for the year. (Easiest way to set this up is to enter 31-Jan-2019 at B3, and then C3 and all succeeding columns on that row are =EOMONTH( B3, 1). Format the entries to just "Mmm" for readability, and they'll look like what you want: Jan Feb Mar ... etc.

In this case, the sumproduct formula that will work for you for each account's sum of amounts for each month on the table is:
=SUMPRODUCT(( Outgoings!$A$3:$A$303) * ( Outgoings!$B$3:$B$303 = $A19) * ( Outgoings!$D$3:$D$303 = B$3))

Remember, Outgoings!D3:D303 is matching the End-of-Month value for each "Date" entered at C3:C303 to match against the "Months" (which you've seen are actually end-of-month dates) at B3:M3.

 

[Marcelo Branco]

Maybe...

Formula in B19 copied across until M19 and down
=SUMPRODUCT(--(MONTH(Outgoings!$B$4:$B$303)=MONTH(1&B$3)),INDEX(Outgoings!$F$4:$Q$303,0,MATCH($A19,Outgoings!$F$2:$Q$2,0)))

Hope this helps

M.

 

[F0RE5T]

Hi Many thanks for your advice and I am sorry if my explanation was a bit confusing. The first part of my formula removed the need for an EOMonth column but thanks. I understand your approach but the approach in the answer below met my requirement 100%

Again many tanks for our reply.

James

 

[F0RE5T]

Hi Many thanks for your advice and I am sorry if my explanation was a bit confusing. The first part of my formula removed the need for an EOMonth column but thanks. I understand your approach but the approach in the answer from Marcelo met my requirement 100%

Again many thanks for our reply.

James

 

[F0RE5T]

Maybe...

Formula in B19 copied across until M19 and down
=SUMPRODUCT(--(MONTH(Outgoings!$B$4:$B$303)=MONTH(1&B$3)),INDEX(Outgoings!$F$4:$Q$303,0,MATCH($A19,Outgoings!$F$2:$Q$2,0)))

Hope this helps

M.

Hi Marcelo

Many thanks for your answer and it met my requirement 100%

Your are a star.

Again many thanks for our reply.

James

 

[Marcelo Branco]

You are welcome. Glad to help.

M.