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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
805
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,101,845
Messages
5,483,271
Members
407,387
Latest member
ajay1303

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top