# Sumproduct multi criteria excel 2016 - Monthly Cash Flow Forecast spreadsheet

#### F0RE5T

##### Board Regular
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
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
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
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

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
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

Again many thanks for our reply.

James

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Glad to help.

M.

Replies
0
Views
98
Replies
0
Views
604
Replies
1
Views
63
Replies
83
Views
1K
Replies
0
Views
2K

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.

### Which adblocker are you using?

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

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