Complex Vlookup "sumif" statement

amwilber

Board Regular
Joined
May 15, 2008
Messages
162
Hello I am building a tracking sheet for finance that will track savings over 10 fiscal years against our plan.

I have 1 sheet with our plan, a second sheet where I am tracking ongoing actual savings we capture.

What i was looking to do was create a sumif statement that would look at my savings sheet and easily count the totals by fiscal year.


Example:

Sheet 1 - Note that I am not showing all columns for sheet 1, I would want to repeat my "Actuals" out to 2030.

Spend CategoryForecast 2021Actuals 2021 VLOOKUP REQUIREDForecast 2022 ......
IT Savings$1,100,000$900,000
Marketing Savings$500,000$200,000



sheet 2 - In this sheet, "All" on fiscal year represents the full time horizon of my sheet 1 - so out to 2030.
Spend CategoryDescriptionFiscal YearTotal Savings
IT SavingsSaving 1ALL$1,000,000
IT SavingsSaving 22021$100,000
Marketing SavingsSaving 1ALL$100,000
Marketing SavingsSaving 2ALL$50,000

Marketing Savings

Saving 3
2023$50,000


Any help on how to best do that vlookup on sheet 1 that gets repeated across fiscal years would be greatly appreciated!
 
I used your data and, assuming I understand your desired results, the formula you need is shown above cell C11, where I wrote it. Each cell shows the sum of 1) the Spend category specified for that year/category in the upper data, plus 1) any savings for "ALL" years in that category. For example, cell G12, Marketing Savings for Actuals in 2023, is the 50K shown for 2023 in that category, plus the 100K+50K of Marketing Savings shown for "ALL" years. That's 200K in total. as shown in G12.

Hope that helps.
 

Attachments

  • Untitled.png
    Untitled.png
    44.9 KB · Views: 6
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Try in C2 copied down
=SUM(SUMIFS(Sheet2!D$2:D$6,Sheet2!A$2:A$6,A2,Sheet2!C$2:C$6,{2021;"ALL"}))

In E2 and G2 just change the year in red

M.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top