COUNTIFS Formula For Total Number Of Requests Per Month Assigned To Contacts

Options
JayRaditch
edited 09/21/20 in Formulas and Functions

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))

Tags:

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!