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

IBG_rs

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!!

Nick Korna

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!

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!

IBG_rs

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

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!

IBG_rs

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

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)?

IBG_rs

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!!