Totals based on date and salesperson on another sheet
Good evening,
I'm looking for some assistance, any help would be appreciated. I'm trying to total the "Install Amount" from my "2020 Sales Tracking Sheet" for each "Account Executive" by "Month" on my "Test - Sales Bonus Tracking" sheet. Both are shown below:
Answers
-
Hi Josh! Try using this formula in your Sales Bonus tracking sheet for the install amount column.
{Install Amount} , {Account Executive(s)}, and {Sold Date}= are external sheet references to the matching columns in your Sales tracking sheet. You'll need to make these sheet references first before the formula can work.
=SUMIFS({Install Amount}, {Sold Date}, MONTH(@cell) = 1, {Account Executive(s)}, [Account Executive]@row)
You'll need to change the Month(@cell) to the corresponding month number (may=5) to total.
-
Thanks cmondo!
This is what it looks like now: =SUMIFS({Install Amount 1}, {Sold Date 1}, MONTH(@cell) = 1, {Account Executive 1}, [Account Executive]@row)
It's coming up unparseable. Any ideas?
-
For whatever reason, I seem to be unable to get MONTH to work. This is working to total the [Install Amount] by date (I'm back to working on this on the same sheet as the data eliminating the cross sheet reference until I get the formula worked out:
=SUMIFS([Install Amount]:[Install Amount], [Sold Date]:[Sold Date], AND(@cell >= DATE(2020, 1, 1), @cell < DATE(2020, 2, 1)))
I cannot seem to get the next part down to additionally filter it by [Account Executive]
-
Just checking, your external references have the entire column selected:
For your second formula try using:
=SUMIFS([Install Amount]:[Install Amount], [Account Executive(s)]:[Account Executive(s)], [Account Executive(s)]@row, [Sold Date]:[Sold Date], >=DATE(2020, 1, 1), [Sold Date]:[Sold Date], <DATE(2020, 2, 1))
With this one you'll need to change the date range to affect the rows you want to sum.
-
Thanks. Just to clarify, the entry bolded below would be the value from the field correct? In this case, the first and last name of my Account Executive.
=SUMIFS([Install Amount]:[Install Amount], [Account Executive(s)]:[Account Executive(s)], [Account Executive(s)]@row, [Sold Date]:[Sold Date], >=DATE(2020, 1, 1), [Sold Date]:[Sold Date], <DATE(2020, 2, 1))
-
You can also try something like this. It will allow you to enter a month and year number instead of having to figure out if this month has 28, 29, 30, or 31 days.
=SUMIFS([Install Amount]:[Install Amount], [Account Executive(s)]:[Account Executive(s)], "John Doe", [Sold Date]:[Sold Date], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))
This will give you John Doe's total for January of 2020.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!