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


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!


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




Please sign in to leave a comment.