# How to get YTD costs with criteria?

#### christian612

##### New Member
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).

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

##### Well-known Member
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.

ABCDEFGHIJKLM
1VendorQuarterFYInvoiceInvoice AmountUpcomingYTD Expenditures
2HR ConsultingQ42020\$2,000.00\$5,023.00
3HR ConsultingQ12021\$500.00\$3,500.00
4HR ConsultingQ22020\$2,000.00\$3,100.00
5HR ConsultingQ22021\$6,000.00\$9,500.00VendorF Consulting
6HR ConsultingQ32020\$77.00\$3,023.00QuarterQ2
7HR ConsultingQ12021\$4,000.00\$3,500.00FY2020
8HR ConsultingQ22020\$1,100.00\$3,100.00YTD Expenditure-\$3,667.00
9F ConsultingQ22020\$7,000.00-\$3,667.00
10F ConsultingQ32020\$2,200.00-\$1,467.00
11F ConsultingQ42020\$1,000.00-\$2,467.00
12F ConsultingQ12020\$3,333.00\$3,333.00

</tbody>
christian612-a

Worksheet Formulas
CellFormula
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)

</tbody>

<tbody>
</tbody>