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
 

BlueHornet

Active Member
Joined
Apr 13, 2012
Messages
332
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,317
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
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,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top