Unsure of formula or function to pull sales agent data from 2nd sheet

Currently we input all weekly sales results for our agents into sheet ONE named "2024 Agent Sales Activity" (1 row per agent with sales that week, with the primary column being the agent name "Last, First", and one of the other column is "Weekly Total").

In a SECOND sheet I'm setting up a lifetime sales tracker, with one column per year, and one row for all active agents. In SECOND sheet's "2024" column I need to:

  1. Look at sheet "2024 Agent Sales Activity" and SUM all "Weekly Totals" for each agent referencing "Last, First"
  2. Then add the 2024 total (from step 1) to the 2023 column total

Thanks for any help you could offer with this formula!!

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @IBG_rs,

    You can use MATCH and SUMIF here:

    =IF(MATCH([Agent Full Name]@row, {2024 IBG Agent Sales Activity Range 2})>0,SUMIF({2024 IBG Agent Sales Activity Range 2},[Agent Full Name]@row,{2024 IBG Agent Sales Activity Range 1}))

    This should say if a MATCH is successful then sum up the agent's earnings over all weeks from your first sheet.

    If you want to add this to the 2023 earnings column then you can tack this onto the formula easily enough (adding +[2023 earnings]@row on the end, for example).

    Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @IBG_rs,

    I think I was able to make something using IF and SUM (couldn't do it with SUMIF for some reason). In the lifetime sales sheet, try this:

    =IF(MATCH([Agent Name]@row, {Agent Name 2024 Range}), SUM({Weekly Totals 2024 Range})

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Thanks for your response. Right now it's combining the weekly sales totals of all agents (like the match function isn't working). This is what I have:

    =IF(MATCH([Agent Full Name]@row, {2024 IBG Agent Sales Activity Range 2}), SUM({2024 IBG Agent Sales Activity Range 1}))

    "[Agent Full Name]@row" is referencing the Agent Full Name cell in sheet 2
    Range 2 is looking at the entire column of "Agent Full Name" in sheet 1
    Range 1 is looking at the entire column of "Weekly Sales Total" in sheet 1

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @IBG_rs,

    You can use MATCH and SUMIF here:

    =IF(MATCH([Agent Full Name]@row, {2024 IBG Agent Sales Activity Range 2})>0,SUMIF({2024 IBG Agent Sales Activity Range 2},[Agent Full Name]@row,{2024 IBG Agent Sales Activity Range 1}))

    This should say if a MATCH is successful then sum up the agent's earnings over all weeks from your first sheet.

    If you want to add this to the 2023 earnings column then you can tack this onto the formula easily enough (adding +[2023 earnings]@row on the end, for example).

    Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!

  • Nick, thank you! Your formula did perfectly sum all of the 2024 sales from the other sheet. However, I tried taking your advice about adding the 2024 sales to the 2023 total (within your formula) and I got an error.

    Would you kindly amend your formula to properly add the 2023 total?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    If your 2023 Sales data is in a column of the same name and on the same row then your formula would be:

    =IF(MATCH([Agent Full Name]@row, {2024 IBG Agent Sales Activity Range 2})>0,SUMIF({2024 IBG Agent Sales Activity Range 2},[Agent Full Name]@row,{2024 IBG Agent Sales Activity Range 1})) + [2023 Sales data]@row

    Or are you needing to pull the 2023 data as well (and if so, from the same sheet as the 2024 or another one)?

  • Nick, that was exactly what I needed. I was overthinking it, as I could have just added the 2023 column to the end like you did. Thanks so much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!