Countif depending on 1)string value 2)date range

Options

Fault type Date raised

Microbial 01/1/2020

Microbial 12/1/2020

Chemical 14/1/2020

Chemical 01/2/2020

Physical 01/3/2020


I have been successful in getting the count for the [fault type] through (e.g.'microbial')

=COUNTIFS({Supplier Performance Range 1}, FIND("Microbial", @cell) > 0)

But I would like to also do this by month, as in real life there will be more than just one incident.

any ideas?

Best Answer

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓
    Options

    So, for instance, you want to see all of the incidents for January 2020?

    Try this variation: =COUNTIFS({Supplier Performance Range 1}, FIND("Microbial", @cell) > 0, [Date Raised]:[Date Raised], Month(@cell) = 1,[Date Raised]:[Date Raised], Year(@cell) = 2020)

    The only thing you should note is that if your Date Raised formula is a cross sheet reference, you'll have to create those ranges as Cross sheet references instead of just listing the range.

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓
    Options

    So, for instance, you want to see all of the incidents for January 2020?

    Try this variation: =COUNTIFS({Supplier Performance Range 1}, FIND("Microbial", @cell) > 0, [Date Raised]:[Date Raised], Month(@cell) = 1,[Date Raised]:[Date Raised], Year(@cell) = 2020)

    The only thing you should note is that if your Date Raised formula is a cross sheet reference, you'll have to create those ranges as Cross sheet references instead of just listing the range.

  • Joe Suh
    Joe Suh ✭✭
    Options

    wow I was expecting a few days for the reply.. I'm currently exploring Smartsheet as an alternative from MS Sharepoint/Power automate... the questions I asked months ago, still no reply... that's a tick! I will give this a try :) thanks so much Mike - I will reply again once i get this working.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Sounds good! :) let us know if you get stuck. There are several of us on here in the community that are fairly quick to reply!

    If you get stuck, providing screenshots (hiding sensitive data) also can help. And if at any point you need to share the sheet that is also a possibility. :) Happy Smartsheeting!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 03/06/20
    Options

    Hi Joe,

    @Joe Suh

    Welcome to the Community and the wonderful world of Smartsheet!

    I hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!