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
1	Vendor		Quarter	FY			Invoice	Invoice Amount	Upcoming	YTD Expenditures
2	HR Consulting		Q4	2020				$2,000.00		$5,023.00
3	HR Consulting		Q1	2021					$500.00	$3,500.00
4	HR Consulting		Q2	2020				$2,000.00		$3,100.00
5	HR Consulting		Q2	2021				$6,000.00		$9,500.00		Vendor	F Consulting
6	HR Consulting		Q3	2020					$77.00	$3,023.00		Quarter	Q2
7	HR Consulting		Q1	2021				$4,000.00		$3,500.00		FY	2020
8	HR Consulting		Q2	2020				$1,100.00		$3,100.00		YTD Expenditure	-$3,667.00
9	F Consulting		Q2	2020					$7,000.00	-$3,667.00
10	F Consulting		Q3	2020				$2,200.00		-$1,467.00
11	F Consulting		Q4	2020					$1,000.00	-$2,467.00
12	F Consulting		Q1	2020				$3,333.00		$3,333.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

christian612-a​

Worksheet Formulas Cell Formula M8 =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) J2 =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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>