Join Sum IF Formula??

Options

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

Tags:

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/05/24
    Options

    @ConnorForm

    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.

  • ConnorForm
    ConnorForm ✭✭✭✭
    Options

    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?

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/05/24
    Options

    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.

  • ConnorForm
    ConnorForm ✭✭✭✭
    Options

    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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!