SUMIFS formula
![Keith](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
=SUMIFS({Completed date}, MONTH(@cell) = [Jan 2018]$1, {S/S}, AND({Rep} = "Paul"))
Hi All,
Trying to get the above formula to work.
Referencing from another sheet. Want to be able to match a sales reps figures per month.
From these columns in the other sheet: Completed date column, Invoice value column and rep column.
So essentially the above formula is trying to pull the sales figures for Paul for Jan 2018.
I've got a formula that will give me sales figures for the whole company for Jan 2018 but now need figures by Rep.
Below is formula that works for company sales figures:
=SUMIF({Completed date range}, MONTH(@cell) = [Jan 2018]$1, {S/S value})
Thanks.
Comments
-
Hi Keith,
I can't actually work out why your company sales figures formula works. Because it shouldn't. SUMIF/SUMIFS work like this:
=SUMIF({What you want to sum}, {the range you want to match criteria against}, "The criteria you want to match")
Your working formula has things around the wrong way (criteria before range) and it strikes me that you're looking to SUM {S/S Value}, not {Completed date range}.
SUMIFS also don't like dates very much, so normally you'd have to put in a workaround formula.
How are your dates formatted? Are they just in MM/DD/YY format and you're looking to match everything with the MM/YY that you have in {Completed date range}?
Assuming this is the case, you could try:
=SUMIFS({S/S Value}, {Completed date range}, LEFT(@cell, 2) + RIGHT(@cell, 2) = 1718, {Rep}, "Paul")
Kind regards,
Chris McKay
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!