Complex Vlookup "sumif" statement

[amwilber]

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 Category	Forecast 2021	Actuals 2021 VLOOKUP REQUIRED	Forecast 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 Category	Description	Fiscal Year	Total Savings
IT Savings	Saving 1	ALL	$1,000,000
IT Savings	Saving 2	2021	$100,000
Marketing Savings	Saving 1	ALL	$100,000
Marketing Savings	Saving 2	ALL	$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!

 

[pliskers]

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.

 

[Marcelo Branco]

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.