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:
- Look at sheet "2024 Agent Sales Activity" and SUM all "Weekly Totals" for each agent referencing "Last, First"
- 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
-
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
-
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 -
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!