Sumproduct multi criteria excel 2016 - Monthly Cash Flow Forecast spreadsheet

F0RE5T

Board Regular
Joined
Nov 4, 2014
Messages
201
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

BlueHornet

Active Member
Joined
Apr 13, 2012
Messages
338
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,653
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Nov 4, 2014
Messages
201
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

Board Regular
Joined
Nov 4, 2014
Messages
201

ADVERTISEMENT

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

Board Regular
Joined
Nov 4, 2014
Messages
201
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
 

Forum statistics

Threads
1,136,256
Messages
5,674,659
Members
419,521
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top