How to get YTD costs with criteria?

[christian612]

https://ibb.co/H4rCTQS

I am looking for formula that will calculate YDT expentitures as sum of invoice amount minus upcoming column for each vendor. Additionally, I am seeking the YTD for each vendors by FY and quarter (above the orange field).

 

[Toadstool]

Hi christian612,

You gave no expected results so I must make some assumptions about the calculations.

You have Quarters out of sequence (rows 1 and 2 are for the same FY but Q4 then Q1) so I assume you do not want a running YTD down the page but a calculated total for that Vendor/FY/Qtr which means the same Vendor/FY/Qtr will show the same YTD value.

I have assumed the Class Start Date and Invoice Date are not material; calculations are based on Vendor, FY and Qtr.

My formula calculates from rows 2 to 999 so you will need to modify if your ranges differ.

	A	B	C	D	E	F	G	H	I	J	K	L	M
Vendor	FY	Invoice	Invoice Amount	Upcoming	YTD Expenditures
HR Consulting
HR Consulting
HR Consulting
HR Consulting	Vendor	F Consulting
HR Consulting	Quarter	Q2
HR Consulting	FY
HR Consulting	YTD Expenditure
F Consulting
F Consulting
F Consulting
F Consulting

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Quarter[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q4[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$2,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$5,023.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q1[/TD]
[TD="align: right"]2021[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$500.00[/TD]
[TD="align: right"]$3,500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q2[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$2,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$3,100.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q2[/TD]
[TD="align: right"]2021[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$6,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$9,500.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q3[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$77.00[/TD]
[TD="align: right"]$3,023.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q1[/TD]
[TD="align: right"]2021[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$4,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$3,500.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2020[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q2[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$1,100.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$3,100.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"]-$3,667.00[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q2[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$7,000.00[/TD]
[TD="align: right"]-$3,667.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q3[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$2,200.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-$1,467.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q4[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$1,000.00[/TD]
[TD="align: right"]-$2,467.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Q1[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$3,333.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$3,333.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

christian612-a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M8[/TH]
[TD="align: left"]=SUMIFS($H$2:$H$999,$A$2:$A$999,$M$5,$D$2:$D$999,$M$7,$C$2:$C$999,"<="&$M$6)-SUMIFS($I$2:$I$999,$A$2:$A$999,$M$5,$D$2:$D$999,$M$7,$C$2:$C$999,"<="&$M$6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2
[/TH]
[TD="align: left"]=SUMIFS($H$2:$H$999,$A$2:$A$999,A2,$D$2:$D$999,D2,$C$2:$C$999,"<="&C2)-SUMIFS($I$2:$I$999,$A$2:$A$999,A2,$D$2:$D$999,D2,$C$2:$C$999,"<="&C2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]