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]

If I understand your details, your formula would look something like this. I have substituted the column headers for the column reference you'll need for the SUMIFS ranges. I broke it up into two SUMIFS,

One that sums the Total Savings column where the Spend Category in Sheet 2 matches Sheet 1, and the Fiscal Year in Sheet 2 matches the right four digits in the Sheet 1 header, and
Second, that sums Total Savings for the Spend Category if it's an ALL fiscal year savings.
You'll have to change some of my references to cell references since you haven't provided the Excel layout. Hope this is clear enough to decipher!

=SUMIFS('Sheet2'!Total Savings,Fiscal Year",right("Actuals 2021",4),'Sheet2'!"Spend Category","Spend Category")+SUMIFS('Sheet2'!Total Savings,Fiscal Year","ALL",'Sheet2'!"Spend Category","Spend Category")

 

[amwilber]

Hey there - thank you so much for answering.

I figured I'd make this a bit easier and remake the exact same example as above embedded.

I was a bit confused on altering the data a bit.

Book1
	A	B	C	D	E	F	G
1	Spend Category	Forecast 2021	Actuals 2021 VLOOKUP REQUIRED	Forecast 2022	Actuals 2022 VLOOKUP REQUIRED	Forecast 2023	Actuals 2023 VLOOKUP REQUIRED
2	IT Savings	$1,100,000.00	$ 1,100,000.00	$2,100,000.00	$ 900,000.00	$ 2,200,000.00	$ 900,000.00
3	Marketing Savings	$ 500,000.00	$ 150,000.00	$ 600,000.00	$ 150,000.00	$ 700,000.00	$ 200,000.00
Synergy

Book1
	A	B	C	D
8	Spend Category	Description	Fiscal Year	Total Savings
9	IT Savings	Saving 1	ALL	$1,000,000
10	IT Savings	Saving 2	2021	$100,000
11	Marketing Savings	Saving 1	ALL	$100,000
12	Marketing Savings	Saving 2	ALL	$50,000
13	Marketing Savings	Saving 3	2023	$50,000
Actuals

 

[pliskers]

Hey there - thank you so much for answering.

I figured I'd make this a bit easier and remake the exact same example as above embedded.

I was a bit confused on altering the data a bit.

Book1
	A	B	C	D	E	F	G
1	Spend Category	Forecast 2021	Actuals 2021 VLOOKUP REQUIRED	Forecast 2022	Actuals 2022 VLOOKUP REQUIRED	Forecast 2023	Actuals 2023 VLOOKUP REQUIRED
2	IT Savings	$1,100,000.00	$ 1,100,000.00	$2,100,000.00	$ 900,000.00	$ 2,200,000.00	$ 900,000.00
3	Marketing Savings	$ 500,000.00	$ 150,000.00	$ 600,000.00	$ 150,000.00	$ 700,000.00	$ 200,000.00
Synergy

Book1
	A	B	C	D
8	Spend Category	Description	Fiscal Year	Total Savings
9	IT Savings	Saving 1	ALL	$1,000,000
10	IT Savings	Saving 2	2021	$100,000
11	Marketing Savings	Saving 1	ALL	$100,000
12	Marketing Savings	Saving 2	ALL	$50,000
13	Marketing Savings	Saving 3	2023	$50,000
Actuals

 

[pliskers]

Hi, Unfortunately you did not upload your Excel sheet, you just pasted in the text and image, so it cannot be downloaded and modified. Try using the Upload Mini-Sheet button.

 

[Marcelo Branco]

amwilber

Don't understand how did you get 900K in E2 and G2. Could you elaborate?

M.

 

[pliskers]

I made no changes to anything you uploaded. Those are your numbers.

 

[Marcelo Branco]

I made no changes to anything you uploaded. Those are your numbers.

What post or who does this refer to?

M.

 

[pliskers]

What post or who does this refer to?

M.

amwilber​

. It’s not that complicated but the uploaded screen shot can’t be pasted into Excel cleanly to illustrate the solution.

 

[amwilber]

I used the mini sheet copying all values this time - did this help?

Book2
	A	B	C	D	E	F	G	H	I
1	Spend Category	Forecast 2021	Actuals 2021 VLOOKUP REQUIRED	Forecast 2022	Actuals 2022 VLOOKUP REQUIRED	Forecast 2023	Actuals 2023 VLOOKUP REQUIRED	Forecast 202….	….
2	IT Savings	$ 1,100,000.00	$ 1,100,000.00	$ 2,100,000.00	$ 1,000,000.00	$ 2,200,000.00	$ 1,000,000.00
3	Marketing Savings	$ 500,000.00	$ 150,000.00	$ 600,000.00	$ 150,000.00	$ 700,000.00	$ 200,000.00
Sheet1

Cell Formulas
Range	Formula
C2	C2	=Sheet2!D2+Sheet2!D3
C3	C3	=Sheet2!D4+Sheet2!D5
E2	E2	=Sheet2!D2
E3	E3	=Sheet2!D4+Sheet2!D5
G2	G2	=Sheet2!D2
G3	G3	=Sheet2!D4+Sheet2!D5+Sheet2!D6

Book2
	A	B	C	D
1	Spend Category	Description	Fiscal Year	Total Savings
2	IT Savings	Description 1	ALL	$ 1,000,000.00
3	IT Savings	Description 2	2021	$ 100,000.00
4	Marketing Savings	Description 3	ALL	$ 100,000.00
5	Marketing Savings	Description 4	ALL	$ 50,000.00
6	Marketing Savings	Description 5	2023	$ 50,000.00
Sheet2