COUNTIFS Formula For Total Number Of Requests Per Month Assigned To Contacts
Hello,
I am trying to troubleshoot a formula to achieve the following outcome.
Outcome:
Count the TOTAL number of rows in which the [Request Date] column is March (3) AND the [Requestor] column is any of the following contacts:
Jesper Poulsen
Libby Martinotti
Michelle Wisbey
Annel Ayala
Anita Miazga
Amanda Ayre
Alex McCarthy
Jill Gibilisco
Background Information:
The Request Date column type is a Auto-Number/System-Generated Column [Created (Dated)].
The Requestor column type is a Contact List that DOES NOT Allow Multiple Contacts In Cell.
(Database Name: Creative Request) -- SCREENSHOT:
(Database Name: Epic Rights Creative Request -- Monthly Data) -- SCREENSHOT:
Current Formula:
=COUNTIFS({Creative Request Active Date}, IFERROR(MONTH(@cell), 0) = 3, {Creative Request ER Active}, FIND("Jesper Poulsen", @cell))
Answers
-
Hi @JayRaditch
Your formula nearly got it. It should be this:
=COUNTIFS({Creative Request Active Date}, IFERROR(MONTH(@cell), 0) = 3, {Creative Request ER Active}, FIND("Jesper Poulsen", @cell)>0)
Since you're working with contact cell, you could also go away with and OR(HAS...) type of function as well:
==COUNTIFS({Creative Request Active Date}, MONTH(@cell) = 3, {Creative Request ER Active}, OR(HAS(@cell, "Jesper Poulsen"), HAS(@cell, "Libby Martinotti"), HAS(@cell, "Michelle Wisbey"), HAS(@cell, "Annel Ayala"), HAS(@cell, "Anita Miazga"), HAS(@cell, "Amanda Ayre"), HAS(@cell, "Alex McCarthy"), HAS(@cell, "Jill Gibilisco")))
Also, if the list of names is the full list, you could replace the whole OR statement with an NOT(ISBLANK(@cell)) argument as well.
Finally, since the request date column is an automated column, the cell will never be blank, so you don't really need the IFERROR statement on the MONTH() function.
Hope it helped!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!