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

• Vivian Mosher

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

• Bryan Robinson

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

• Vivian Mosher

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.