How to get YTD costs with criteria?

christian612

New Member
Joined
Sep 9, 2019
Messages
1
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).:)
 

Some videos you may like

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
818
Office Version
2016
Platform
Windows
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

<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
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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,102,356
Messages
5,486,377
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top