Trying to only pull the month from a cell if the a certain drop box is picked

I am trying to count the amount of projects in a month with the date entered but only pull it out if a the drop down box says archived, I believe the month count is correct as that part alone works but when trying to add the code for the dropdown box it does not work, any help would be apricated, code below;

=COUNTIFS({Project Quality Engineer Project Updates Range 8}, <=DATE(2022, 3, 31)) - COUNTIF({Project Quality Engineer Project Updates Range 8}, <=DATE(2022, 3, 1)) COUNTIFS({Project Quality Engineer Project Updates Range 5}, "archived")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this instead:

    =COUNTIFS({Project Quality Engineer Project Updates Range 8}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022), {Project Quality Engineer Project Updates Range 5}, @cell = "Archived")


    NOTE: I adjusted the overall formula so that you only have a single COUNTIFS to save a little bit of typing and some back end efficiency.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • A_Dobson
    A_Dobson
    edited 10/04/22

    Perfect, thanks for your help

    Adam

  • Is there a quick way to do all except one item in the dropdown box. for example, all except Archived

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @A_Dobson

    I hope you're well and safe!

    Try something like this.

    =COUNTIFS({Project Quality Engineer Project Updates Range 8}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022), {Project Quality Engineer Project Updates Range 5}, @cell <> "Archived")

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

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

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @A_Dobson You would switch the argument from equals (=) to not equal to (<>).

    =COUNTIFS({Project Quality Engineer Project Updates Range 8}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022), {Project Quality Engineer Project Updates Range 5}, @cell <> "Archived")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!