sum all of the net sales for each sales person

System

This discussion was created from comments split from: SUMIFS with multiple matches.

NikkiOno

@Paul Newcome

I have something similar i am trying to do. i need to sum all of the net sales for each sales person in (daily sales activity sheet) IF the sales associate matches the line on the summary data sheet. pic below.

the formula i was trying to use was =SUMIFS({NET SALES}, {LOCATION}, "UWM", {SALES ASS}, [SALES ASSOCIATE]@row) but its only giving me some of the sales, not all of them and i cant figure out why.

SUMMARY DATA SHEET

DAILY SALES SHEET

Jeff Reisman

@NikkiOno

Your formula looks good, so this may just be a data problem.

It could be something as small as an imperceptible extra space somewhere in the name in some entries in the Sales Associate column. You can check this by running two different COUNTIFS and comparing the results:

=COUNTIFS({SALES ASS}, "CODY HOPPES")

=COUNTIFS({SALES ASS}, CONTAINS("CODY", @cell))

If the first count is less than the second count, then you know you have cells for Cody that don't quite match your parameter from the summary sheet.

NikkiOno

Ahh that makes sense. i spent hours last night trying to figure it out. I will try and let you know if it doesn't work.

thanks so much!

NikkiOno

@Jeff Reisman

unfortunately that didnt work. all the numbers are coming up the same and i tried it even a third way. but the total sales are still off.

Jeff Reisman

@NikkiOno How about your dollar values? Are you positive they are all stored as numbers and not text? If they are numbers stored as text then SUMIFS will ignore them. If you can create a helper column in your data sheet, convert the values in the Net Sales column to number using the VALUE function =VALUE([Net Sales]@row) and then point your SUMIFS at the helper column and see what you get.

NikkiOno

@Jeff Reisman That didnt work either. I keep getting $478,167.50 when it should be $1,833,964 for Cody. I get $0 for Alec Bakker and it should be $280,101.28. I must be doing something wrong.

NikkiOno

@Jeff Reisman omg, im an idiot!! I was adding in another criterion (location), and i didn't have a location by all the names so it wasn't adding those. I am so sorry!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/355751#Comment_355751

😂 Hey at least you figured it out! Like I said, your formula looked great so I knew it had to be the data!