COUNTIF and not blank for flags

Options

Hi

I'm trying to count the number of items which are not flagged across an entire column, but don't want to include blanks in the count.

As line items are added, it will calculate if it's at risk or not at risk and add it to the count.

If the lines in the column are blank, they're ignored.

I've attached a screenshot to illustrate.

Thanks for any help you can provide.

Ebony


Best Answer

  • Jeff M.
    Jeff M. ✭✭✭
    Answer ✓
    Options

    Hi,

    You could add a helper column with the formula =IF([At Risk]@row = 0, 1, 0)

    Then just sum that column in your Count of Not At Risk column. =SUM([Helper]:[Helper])

    Hopefully that works for you.

Answers

  • Jeff M.
    Jeff M. ✭✭✭
    Answer ✓
    Options

    Hi,

    You could add a helper column with the formula =IF([At Risk]@row = 0, 1, 0)

    Then just sum that column in your Count of Not At Risk column. =SUM([Helper]:[Helper])

    Hopefully that works for you.

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

    Hi @EbonyG

    I hope you're well and safe!

    To add to Jeff's excellent advice/answer.

    Here's another option.

    =COUNTIF([At Risk]:[At Risk], "0") - 10
    

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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.

  • EbonyG
    EbonyG ✭✭
    Options

    Hi @Jeff M. and @Andrée Starå

    Thank you both for your answers. I really appreciate your time.

    Both of them help greatly. My only query is if the formula can only count the rows which have data against them, not just having the flag unchecked.

    For example, if I have rows which have an unchecked flag in them, but there is no data to the left of them, is it possible to dynamically update the formula so that if I enter data to the left of the unchecked flag, it adds it to "Not at Risk", rather than just counting the flags as a whole? I basically want to not have to update the formula each time I add a row of data.

    I hope that makes sense.

    If this is not possible, I'm able to work with the answers given. Should you need clarification, I am happy to provide it.

    With thanks again, and kind regards,

    Ebony

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

    @EbonyG

    Happy to help!

    Not sure I follow! What do you mean by "data to the left of them"?

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful. 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.

  • EbonyG
    EbonyG ✭✭
    Options

    Hi @Andrée Starå

    I've included a screenshot to illustrate.

    Essentially, when a new line of date is added, it seems to generate a blank flag. I would like to dynamically be able to count the status of the flags (At Risk or Not At Risk) without needing to drag the formula down each time a new line is added.

    I hope this explanation and screenshot help clarify what I'm after. I'm very happy to answer any questions to help get to the bottom of this.

    With thanks and best regards,

    Ebony


  • Jeff M.
    Jeff M. ✭✭✭
    Options

    I think I understand your question. A possible solution would be to use another symbol other than the flag. You could use the Red, Yellow, Green light instead. This way a new row can be added without a status symbol automatically appearing. Then by using the status type to trigger the symbol, you could then effectively count "Red" vs "Green", using the same method.

    =IF(Status@row = "Green", 1)

    =IF(Status@row = "Red", 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!