0

Summing sales rep totals from list for multiple codes into one cell

Vivian Mosher

May 01, 2020 22:33

None

Follow

Hello!

I need to sum the line totals for all of the sales codes for a single rep into a cell.  I am using an NL function to create the list of sales for all sales reps and then totaling the sales in another sheet for each individual rep.  I used SUMIF and that worked with one sales code, but when I used a1|a2|a3 for the sum range, it didn't work.

In the example below, I need to sum all of the sales codes for each rep in the Total Sales Column.  What would be the best way to do this?  Thanks!

Example:

Rep	Sales Codes	Total Sales
Ann	A1|A2|A3|A4|A5
Bob	B1|B2|B3
Chris	C1|C2|C3|C4

 

Salesperson	Sales $
A1	50
B3	20
A1	100
C4	30
C4	60
A3	20
A5	70
B1	100
C2	20
C3	50
C3	70

 

 

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Vivian Mosher May 04, 2020 19:58

  I figured out how to achieve what I needed.  I ended up using a SUMIF with an array constant and that works.

  =SUM(SUMIF(region,{"West","North"},amount))

   

• 0
  
  Bryan Robinson May 05, 2020 18:49

  You can use the NL(Sum) and filter on the sales codes in your first screenshot. In the Total Sales column, something like NL("Sum","Sales Table","Sales Amount","Sales Code",<<REFERENCE TO CODES>>)

• 0
  
  Vivian Mosher May 05, 2020 20:22

  Thanks for the response, Bryan!  I can't use the NL Sum.  I have to use a NL grabbng the rows because I need to do a calculation of the cost times the quantity to get the amount of the line.  We don't have the amount as a field in the table.