Count If and Not blank

Options

Hey everyone, I am trying to count cells in a column where one column is not blank and cells in the other column matches a certain criteria.

The columns are:

Responsible Department - I want to count the cells in this column that are not blank

and

Program Name - Here is the Criteria - I want to count the cells in the Responsible Department column if the cell in this column matches "USP"


I have looked through the forums and can't seem to find a formula that would work (or one that I am doing right :) )


Thanks in advance for any help!

Tags:

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @EmilyE

    Hope you are fine, please try the following formula:

    =IFERROR(COUNTIFS([Responsible Department]:[Responsible Department], NOT(ISBLANK(@cell)), [Program Name]:[Program Name], "USP"), "")


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @EmilyE

    Hope you are fine, please try the following formula:

    =IFERROR(COUNTIFS([Responsible Department]:[Responsible Department], NOT(ISBLANK(@cell)), [Program Name]:[Program Name], "USP"), "")


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • EmilyE
    EmilyE ✭✭✭
    Options

    That did it!

    Thank you so much! I haven't had much experience with the ISBLANK formulas.

    I appreciate your help!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @EmilyE 

    You are welcome and I will be happy to help you any time. Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful". 

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @EmilyE

    Thanks for the nice and quick response

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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

    Hi @EmilyE

    I hope you're well and safe!

    To add to Bassam's excellent advice/answer.

    Here's another option for the ISBLANK part that I personally prefer.

    =IFERROR(COUNTIFS([Responsible Department]:[Responsible Department], <>"", [Program Name]:[Program Name], "USP"), "")

    I hope that helps!

    Be safe and have a fantastic week!

    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.

  • Valerie_WPA
    Options

    Hello!

    I thought this would work, and I'm sure it's my syntax. But help please.

    I'm trying to get a % complete for Jan but only if the January Date is not blank and not NA.

    Basically count a date only.



  • Genevieve P.
    Options

    Hi @Valerie_WPA

    When you're looking for text in a formula it needs to be in Quotes, like so: "NA"

    Also, your range is looking only into the one cell in this row with @row.

    Are you wanting to look into the whole column to Count how many rows are not blank and not "NA"?

    Try this instead:

    =COUNTIFS(January:January, <> "NA", January:January, <> "")

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!