sum all of the net sales for each sales person
Answers
-
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
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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!
-
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.
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@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.
-
@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!
-
😂 Hey at least you figured it out! Like I said, your formula looked great so I knew it had to be the data!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!