Join Sum IF Formula??
Good Day all, just checking to see if this kind of formula is even possible so bear with me for the explanation. I have a list of Dealers broken out by their budget names. So Dealer Name can have a 1 to many relationship with Budget Name. I need a formula that will total the budget for a given Dealer. For this Example: "Ag Pro Carnesville - Ag & Turf" is referenced three times as it has 3 different Budget Names. For my formula and use case I need it total the budget per Dealer Name not budget name even though the sheet is broken out by Budget Name. Is there a formula that can calculate the total for "Ag Pro Carnesville - Ag & Turf" to read as 15.00? These monthly outputs feed two other Smartsheets - One sheet is broken down by Budget name (So Dealer Name referenced more than once) the other sheet is broken down by Dealer Name( So it is only referenced once - this is where the formula I am asking about will go)
Source Sheet
Target Sheet for formula Jun Act, Goal is formula to output $15.00 for the 3 $5.00 Ag Pro Carnesville referenced in the source sheet
Best Answer
-
Then yes.
=SUMIFS({Jun Ref},{Dealer Ref},Dealer@row)
Should work.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
I will use a naming system for your source sheet for references. EX the Jun Column in your source sheet will have {Jun Ref} Just replace my ref name with what ever you want to name it.
=SUMIFS({Jun Ref},{Dealer Ref},"Ag Pro Carnesville - Ag & Turf")
You can get more in depth and have it work with a Index match as well. by creating a reference table for the Retailer and Dealer
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thanks @Mark.poole , just to note "Ag Pro Carnesville - Ag & Turf" is not the only dealer name, there are 174 other Dealer Names, is there to make the Criterion just Dealer@row?
-
You wouldn't be able to make it Dealer@row. Cause the Criterion would be Retailer@row. How ever since Retailer does not match the name of the dealer. "Unless you also have Retailer on the reference sheet, Or Dealer on the Summary sheet I could not tell based on the screen shot. " You need something on the summary sheet that matches what your referencing to be able to do @row.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Apologies @Mark.poole I already do have a Dealer X Retailer Lookup Table in a separate SS. SO this Retailer name of 19256 - AG-PRO - A&T Carnesville is synced to Dealer name Ag Pro Carnesville - Ag & Turf. I could not fit it in the screenshot, but there is a dealer@row in the summary source sheet so the Dealer Names are synced across the 3 sheets
-
Then yes.
=SUMIFS({Jun Ref},{Dealer Ref},Dealer@row)
Should work.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!