Count If and Not blank

EmilyE
EmilyE ✭✭✭✭

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 βœ“

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

    1.jpg


    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 βœ“

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

    1.jpg


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

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

    @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 ✭✭✭✭✭✭

    @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Γ₯ Community Champion

    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
    Valerie_WPA ✭✭

    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.


    image.png


  • 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, <> "")

    Need more information? πŸ‘€ | Help and Learning Center

    こんにけは (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!πŸ‘‹ | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!