Join Sum IF Formula??

ConnorForm

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

Mark.poole

Then yes.

=SUMIFS({Jun Ref},{Dealer Ref},Dealer@row)

Should work.

Mark.poole

@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

ConnorForm

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

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.

ConnorForm

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

Then yes.

=SUMIFS({Jun Ref},{Dealer Ref},Dealer@row)

Should work.