SUMIFS using Dictionary

[inactiveUser462638]

Hi Excel Champs,

Please help me getting this calculated. I'm using SUMIFS formulas now but it's going very slow.

I have data like this:

Element Type	Inflow/Outflow	Forecast ID	Element Description	Date	Recording Person	Vendor / Customer	Gross Amount
HR Costs	Outflow		Refund Andre	01.01.2020	Phil	Customer1	18.00
Technology Services	Outflow		Subscription	01.01.2020	Phil	Customer2	45.00
Loans and Taxes	Inflow		Subscription	01.02.2020	Phil	Customer2	45.00
Office Costs	Outflow		Subscription	01.03.2020	Phil	Customer2	36.00
Travel, Accommodation, Expenses	Outflow		Income Tax	01.04.2020	Phil	Customer3	1337.79
Technology Services	Inflow		Subscription	01.04.2020	Phil	Customer2	36.00
Loans and Taxes	Outflow		To Customer4	01.05.2020	Phil	Customer4	0.00
Loans and Taxes	Inflow		Subscription	01.05.2020	Phil	Customer2	36.00

I need to make a CashFlow report out of above data like this:

Date	Beginning Balance	Cash Inflows	Cash Outflows	Net Cash Flows	Ending Cash Balance
01.01.2020	100000	=SUMIF()	=SUMIF()	=C2-D2	=B2+E2
02.01.2020	=F2	=SUMIF()	=SUMIF()	=C3-D3	=B3+E3
03.01.2020	=F3	=SUMIF()	=SUMIF()	=C4-D4	=B4+E4

Can you please help me with a VBA code that calculates this using the Dictionary?

Thank you very much!

 

[DanteAmor]

Hi and welcome to MrExcel.

Yes it is possible to do the VBA code.
But I would have to assume all the calculations.
You could put a sample of your data and a sample of your result, using XL2BB tool, look at my signature.

Do you want to get these results on the same sheet or on another sheet?
Do you want to get them by pressing a button?
How many records do you have on your base sheet?

 

[inactiveUser462638]

Hello and thanks for your fast response!

I want to get the results on another sheet.
It will be by pressing a button or running the macro. (I know how to do that)

Let me give you an example of something similar that I found here:

Sumifs VBA with multiple criteria in multiple cells
Marcelo Branco's code was amazing and ran pretty fast but I did not understand how to make it to feet my data.

Thanks for your support.

 

[inactiveUser462638]

I used XL2BB to show you how my - Forecast Elements Sheet looks like and in the second one - Calculation Sheet - how my results should be:

CashFlow Planning 2020 - Automation Workbook.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Element Type	Inflow/Outflow	Forecast ID	Element Description	Date	Recording Person	Vendor / Customer	Gross Amount	Paying Entity	ID Count	Date Validator	Cost Type	Payment Status	Payment to NGI RO
21	HR Costs	Outflow		Some company	02.01.2020	Phil	Some company	40.00	UK			Recurring DD	Paid	N/A
22	HR Costs	Outflow		Some company	02.01.2020	Phil	Some company	39.00	RO			Recurring	Paid	02/12/2019
23	HR Costs	Inflow		Refund for Canceled Flights	02.01.2020	Phil	Some company	111.50	UK			One Time Payment	Paid	N/A
24	HR Costs	Outflow		Some company	02.02.2020	Phil	Some company	39.00	UK			Recurring DD	Paid
25	HR Costs	Outflow		Some company	02.03.2020	Phil	Some company	39.00	UK			Recurring DD	Paid	N/A
26	HR Costs	Outflow		Some company	02.03.2020	Phil	Some company	33.00	RO			Recurring	Paid	03/03/2020
27	HR Costs	Outflow		Service Charge	02.03.2020	Phil	Some company	34.00	UK			Recurring DD	Paid	N/A
28	HR Costs	Outflow		Some company	02.03.2020	Phil	Some company	9.00	RO			Recurring	Paid	03/03/2020
29	HR Costs	Outflow		Some company	02.04.2020	Phil	Some company	39.00	UK			Recurring DD	Paid	N/A
30	HR Costs	Outflow		Some company	02.05.2020	Phil	Some company	39.00	UK			Recurring DD	Paid	N/A
31	HR Costs	Inflow		Some company	02.05.2020	Phil	Some company	119666.80	UK			Recurring	Paid	N/A
32	HR Costs	Outflow		Some company	02.06.2020	Phil	Some company	39.00	UK			Recurring DD
Forecast Elements

Cell Formulas
Range	Formula
H31	H31	=119666.8

CashFlow Planning 2020 - Automation Workbook.xlsx
	A	B	C	D	E	F
1	Date	Beginning Balance	Cash Inflows	Cash Outflows	Net Cash Flows	Ending Cash Balance
2	01.01.2020	100000			=C2-D2	=B2+E2
3	02.01.2020	=F2			=C3-D3	=B3+E3
4	03.01.2020	=F3			=C4-D4	=B4+E4
Calculation Sheet

Cell Formulas
Range	Formula
E2:E4	E2	=C2-D2
F2:F4	F2	=B2+E2
B3:B4	B3	=F2

 

[DanteAmor]

=SUMIF()

But I'm interested in seeing the sumif formulas.
Your results Calculation Sheet is empty.
Could you give more examples on your sheet "Calculation Sheet"
Answer this: How many records do you have on your sheet "Forecast Elements"

 

[inactiveUser462638]

Hello @DanteAmor!

We have like 1000 records now but we use that in multiple sheets with multiple formulas.

That's how the formula is looking like now but I have to transpose the data as presented below:

CashFlow Planning 2020 - Automation Workbook Final.xlsm
	A	B	C	D	E	F	G	H
42	Cash Flow Overview June	2020/06/01	2020/06/02	2020/06/03	2020/06/04	2020/06/05	2020/06/06	2020/06/07
43	Beginning Balance	£120,000.00	£120,000.00	£120,000.00	£120,000.00	£120,000.00	£120,000.00	£120,000.00
44	Cash Inflows	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
45	Cash Outflows	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
46	Net Cash Flows	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
47	Ending Cash Balance	£120,000.00	£120,000.00	£120,000.00	£120,000.00	£120,000.00	£120,000.00	£120,000.00
DailyCashFlow Planning

Cell Formulas
Range	Formula
C43:H43	C43	=B47
B44:H44	B44	=SUMIFS('Forecast Elements'!$F2:$F1000,'Forecast Elements'!$C2:$C1000,"Inflow",'Forecast Elements'!$B2:$B1000,'DailyCashFlow Planning'!B42)
B45:H45	B45	=SUMIFS('Forecast Elements'!$F2:$F1000,'Forecast Elements'!$C2:$C1000,"Outflow",'Forecast Elements'!$B2:$B1000,'DailyCashFlow Planning'!B42)
B46:H46	B46	=B44-B45
B47:H47	B47	=B43+B46

 

[inactiveUser462638]

Hello @DanteAmor,

It seems that the formulas are not pretty accurate. That should be the SUMIF function for the Cash Inflow Column =SUMIFS('Forecast Elements'!$H2:$H1000,'Forecast Elements'!$B2:$B1000,"Inflow",'Forecast Elements'!$E2:$E1000,'DailyCashFlow Planning'!G84)

 

[DanteAmor]

I'm very confused.
In which sheet do you want the results, in "DailyCashFlow Planning" or "Calculation Sheet"?
Do you really want the results in cell A43?

Are the values in cells B43, C43, D43 ... fixed?

just show the examples of what you already have, do not reconstruct formulas or examples.

 

[inactiveUser462638]

I want the results in the Calculation Sheet.



I have to check the date and if it's inflow or outflow and to sum up the values from the Gross Amount Column. The cash flow will be for every day.







That is the Sheet from where I'm taking the data:

CashFlow Planning 2021.xlsx
	A	B	C	D	E	F	G	H	I			L	M	N
1	Inflow/Outflow	Element Type	Forecast ID	Cost Description	Date	Recording Person	Vendor / Customer	Gross Amount	Paying Entity			Cost Type	Payment Status	Payment to Someone
2	Outflow	Technology Services		Some company	2021/01/01	Phil	Some company	40	UK			Recurring DD		N/A
3	Outflow	Technology Services		QA Software	2021/01/01	Phil	Some company	91	UK			Recurring DD
4	Outflow	Loans and Taxes		Some company	2021/01/02	Phil	Some company	39	UK			Recurring DD
5	Inflow	Loans and Taxes		Some company	2021/01/02	Phil	Some company	39	UK			Recurring DD
6	Outflow	Office Costs		Some company	2021/01/02	Phil	Some company	39	RO			Recurring
7	Outflow	Technology Services		Some company	2021/01/06	Phil	Some company	850	UK			Recurring DD
8	Outflow	Loans and Taxes		Corporate Premium Plan Fee	2021/01/01	Phil	Some company	100	UK			Recurring DD		N/A
9	Outflow	Technology Services		Some company	2021/01/06	Phil	Some company UK	120	UK			Recurring DD
10	Inflow	Technology Services		Some company	2021/01/06	Phil	Some company UK	120	UK			Recurring DD
Forecast Elements

And that the sheet where I need my results:

CashFlow Planning 2020 - Automation Workbook Final.xlsm
	A	B	C	D	E	F
1	Date	Beginning Balance	Cash Inflows	Cash Outflows	Net Cash Flows	Ending Cash Balance
2	01.01.2020	£100,000.00	0	0	£0.00	£100,000.00
3	02.01.2020	£100,000.00	0	0	£0.00	£100,000.00
Calculation Sheet Final Form

Cell Formulas
Range	Formula
C2:C3	C2	=SUMIFS('Forecast Elements'!$H:$H,'Forecast Elements'!$A:$A,"Outflow",'Forecast Elements'!$E:$E,A2)
D2:D3	D2	=SUMIFS('Forecast Elements'!$H:$H,'Forecast Elements'!$A:$A,"Outflow",'Forecast Elements'!$E:$E,A2)
E2:E3	E2	=C2-D2
F2:F3	F2	=B2+E2
B3	B3	=F2

 

[DanteAmor]

=SUMIFS('Forecast Elements'!$H:$H,'Forecast Elements'!$A:$A,"Outflow",'Forecast Elements'!$E:$E,A2)
=SUMIFS('Forecast Elements'!$H:$H,'Forecast Elements'!$A:$A,"Outflow",'Forecast Elements'!$E:$E,A2)

Your 2 sumif formulas are the same, but I suppose one is Inflow and the other is Outflow.

=SUMIFS('Forecast Elements'!$H2:$H1000,'Forecast Elements'!$B2:$B1000,"Inflow",'Forecast Elements'!$E2:$E1000,'DailyCashFlow Planning'!G84)

I don't understand why so many changes in the formula.
Could you confirm if I take the structures of the sheets as final from post #9?