Cost/Month based on a range

[inactiveUser462638]

Hello Excel Champs!

Please help me to find the right formula for calculating the cost/month/day based on a range.

I have data like that:

P&L.xlsx
	A	B	C	D	E	F	G	H
1	Inflow/Outflow	Element Type	Element Description	Starting date	End Date	Vendor / Customer	Gross Amount	Paying Entity
2	Outflow	HR Cost	Phil	27-Jan	16-Aug	Bob	100.00	…
3	Inflow	BAU	GB	15/09/2019	15/02/2020	GB	25	…
4	Inflow	BAU	GB2	11/09/2019	15/01/2020	GB	1500	….
P&L Elements

And I have to generate in a new sheet the cost/month based on a date range and to check if this is an Inflow or an Outflow:

P&L.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	P&L Overview	January	February	March	April	May	June	July	August	September	October	November	December	January2	February3
3	Inflow	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	Outflow	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	Gross Profit	0	0	0	0	0	0	0	0	0	0	0	0	0	0
P&L Report

Cell Formulas
Range	Formula
B3:O3	B3	=SUMIFS('P&L Elements'!$G:$G,'P&L Elements'!$A:$A,"Inflow",'P&L Elements'!$D:$D,'P&L Report'!B2)
B4:O4	B4	=SUMIFS('P&L Elements'!$G:$G,'P&L Elements'!$A:$A,"Outflow",'P&L Elements'!$D:$D,'P&L Report'!B2)
B5:O5	B5	=B3-B4

So I have to generate into the January column the total amount of Inflow items/Outflow Items on that month but because I have a Starting Date and an End Date I have to split the amount/day for each item and then to sum the others Inflow/Outflow items that are in the January month (01/01/2020-31/01/2020).

Do you have any idea how I can calculate that?

Thank you!

 

[mamady]

Hi Sergiu,

You need a few modifications

1- From (P&L Report) sheet, type 01/01/2019 in cell B2, 01/02/2019 in cell C2 and drag it till cell O2
2- Select range B2:O2, press Ctrl+1 >> Customs >> select mmm-yy

And then, try the following modified formula, please:
=SUMIFS('P&L Elements'!$G:$G,'P&L Elements'!$A:$A,"Inflow",Month('P&L Elements'!$D:$D),Month('P&L Report'!B2))
=SUMIFS('P&L Elements'!$G:$G,'P&L Elements'!$A:$A,"Outflow",Month('P&L Elements'!$D:$D),Month('P&L Report'!B2))

Let me know

Best Regards
M. Yusuf

 

[mamady]

Correction, below are the correct formulas

=SUMIFS('P&L Elements'!$G:$G,'P&L Elements'!$E:$E,">="&B1,'P&L Elements'!$E:$E,"<="&EOMONTH(B1,0),'P&L Elements'!$A:$A,"Inflow")
=SUMIFS('P&L Elements'!$G:$G,'P&L Elements'!$E:$E,">="&B1,'P&L Elements'!$E:$E,"<="&EOMONTH(B1,0),'P&L Elements'!$A:$A,"Outflow")

 

[inactiveUser462638]

Hello @mamady !

Thanks for your help but unfortunately I need the calculation to be made splitting the Gross Amount/day and the Inflow/Outflow amount to be calculated summing up the partial month amount that is included in the date ranges from P&L Elements and then to show in the P&L Report the results/month.

 

[inactiveUser462638]

You can find my solution here: Converting a Function to VBA Code