Sum If Vlookup Help

A

amwilber

Board Regular
Joined
May 15, 2008
Messages
162
Hello, I am trying to do a vlookup across two different tables.

In this table, I have an actuals spend (2021A) field. I want to look at the 2nd table and sum the total spend with the matching category and fiscal year. In this spreadsheet I will end up with a matching column for all future fiscal years (out through 2030) and will repeat this sumif out through those columns. The sumif should could any fiscal year that matches "2021" OR "All" from the second table.

Synergies Spend Tracker.xlsx
ABC
1Spend Category2021P2021A
2IT synergy (Workday & Salesforce)$0.9
3Other synergy (RealEstate)$0.5
Sheet1



Synergies Spend Tracker.xlsx
ABCD
1Spend CategoryDescriptionFiscal YearSavings Total
2IT synergy (Workday & Salesforce)Savings 12021$0.90
3Other synergy (RealEstate)Savings 2All0.02
4IT synergy (Workday & Salesforce)Savings 320220.2
Actuals Tracking


Any help would be greatly appreciated!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
what does ALL mean ?
SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100 , Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, Left(Sheet1!C$1,4) )

Change the Range from 100 to whatever your range would be

Book1
ABC
1Spend Category20212021A
2IT synergy (Workday & Salesforce)0.90.9
3Other synergy (RealEstate)0.50
Sheet1
Cell Formulas
RangeFormula
C2:C3C2=SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, LEFT(Sheet1!C$1,4) )


Book1
ABCD
1Spend CategoryDescriptionFiscal YearSavings Total
2IT synergy (Workday & Salesforce)Savings 120210.9
3Other synergy (RealEstate)Savings 2All0.02
4IT synergy (Workday & Salesforce)Savings 320220.2
Actuals Tracking


IF all applies to any fiscal

then you could just add another SUMIFS()
SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100 , Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, Left(Sheet1!C$1,4) ) +
SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100 , Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, "ALL")

Depends on how you setup the sheet data for Future Fiscal Years as to whether you can simply copy and past the formula across
 
Upvote 0
updated to include additional fiscal years 2022 and ALL, I dragged the formula across and then just overwrite the P columns

Book1
ABCDE
1Spend Category2021P2021A2022P2022A
2IT synergy (Workday & Salesforce)0.90.9?0.2
3Other synergy (RealEstate)0.50.02?0.02
Sheet1
Cell Formulas
RangeFormula
C2:C3,E2:E3C2=SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, LEFT(Sheet1!C$1,4) )+SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, "ALL")


Book1
ABCD
1Spend CategoryDescriptionFiscal YearSavings Total
2IT synergy (Workday & Salesforce)Savings 120210.9
3Other synergy (RealEstate)Savings 2All0.02
4IT synergy (Workday & Salesforce)Savings 320220.2
Actuals Tracking
 
Upvote 0
etaf said:
updated to include additional fiscal years 2022 and ALL, I dragged the formula across and then just overwrite the P columns

Book1
ABCDE
1Spend Category2021P2021A2022P2022A
2IT synergy (Workday & Salesforce)0.90.9?0.2
3Other synergy (RealEstate)0.50.02?0.02
Sheet1
Cell Formulas
RangeFormula
C2:C3,E2:E3C2=SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, LEFT(Sheet1!C$1,4) )+SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, "ALL")


Book1
ABCD
1Spend CategoryDescriptionFiscal YearSavings Total
2IT synergy (Workday & Salesforce)Savings 120210.9
3Other synergy (RealEstate)Savings 2All0.02
4IT synergy (Workday & Salesforce)Savings 320220.2
Actuals Tracking
Click to expand...


Thank you for your help!

That worked really well!

One additional thing - I might have some savings that are only for a few years, like 2021,2022,2023. How would I alter this lookup to look at that range?
 
Upvote 0
not sure i follow
if the values are not listed for the fiscal years then that would show 0

=SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, LEFT(Sheet1!C$1,4) )
so the last part - LEFT(Sheet1!C$1,4) looks for the fiscal and gets that from the header and then looks up the list

so if NOT in the list say for 2026 , then it would not be found and just return 0

we are not using any LOOKUP, just a SUM IF the criteria is met

show an example of what you mean
 
Upvote 0
1632494956191.png


Something like this... We might have expenses that are savings for only 2 years, while some other ones are "ALL". I am wondering how i handle the 2022;2023 calculation in there. Does that make sense?
 
Upvote 0
thanks,
That maybe difficult as i suspect you could have a whole lot of different years 2 years, 3,4,5,6,7,8,9,10 and will need to extract the years to see if applies

I assume you would not be able to just have 2,3,4,5, etc rows entered one row per year
 
Upvote 0
etaf said:
thanks,
That maybe difficult as i suspect you could have a whole lot of different years 2 years, 3,4,5,6,7,8,9,10 and will need to extract the years to see if applies

I assume you would not be able to just have 2,3,4,5, etc rows entered one row per year
Click to expand...
I was thinking the same thing. I guess I will just do multiple rows if an entry goes across just a couple of years vs. all or a single year. Thank you for your help!
 
Upvote 0
OK, sat back and thought a minute - maybe a wildcard * front and back of the year would work
=SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100,"*"&LEFT(Sheet1!E$1,4) &"*")+SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, "ALL")

Book1
ABCDE
1Spend Category2021P2021A2022P2022A
2IT synergy (Workday & Salesforce)0.9400?400
3Other synergy (RealEstate)0.50.02?0.02
Sheet1
Cell Formulas
RangeFormula
C2C2=SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100,"*"&LEFT(Sheet1!E$1,4) &"*")+SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, "ALL")
C3,E3C3=SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A3, 'Actuals Tracking'!$C$2:$C$100, LEFT(Sheet1!C$1,4) )+SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A3, 'Actuals Tracking'!$C$2:$C$100, "ALL")
E2E2=SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, "*"&LEFT(Sheet1!E$1,4) &"*")+SUMIFS('Actuals Tracking'!$D$2:$D$100, 'Actuals Tracking'!$A$2:$A$100, Sheet1!$A2, 'Actuals Tracking'!$C$2:$C$100, "ALL")


Book1
ABCD
1Spend CategoryDescriptionFiscal YearSavings Total
2IT synergy (Workday & Salesforce)Savings 120210.9
3Other synergy (RealEstate)Savings 2All0.02
4IT synergy (Workday & Salesforce)Savings 32021;2022400
Actuals Tracking


NOT TESTED FULLY
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Sumproduct with 3 tables?
Replies
4
Views
218
killerstein
K
S
  • Question
Macro to add sum total to track percentages
Replies
0
Views
107
spenserallen7
S
W
  • Solved
LOGIC/SUM/FILTER Help
2
Replies
14
Views
521
Alex Blakenburg
A
J
  • Solved
Sum row range based on column header criteria
Replies
6
Views
479
ExceLoki
ExceLoki
A
  • Solved
Conditional macro
Replies
8
Views
156
Fluff
Fluff

Forum statistics

Threads
1,215,073
Messages
6,122,975
Members
449,095
Latest member
Mr Hughes

Share this page

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