COUNTIF EXCEPT

Hello, could you help me find a formula that counts all except "x".

I have a Column that has data such as: dates, "ok" and "N/A". I try using the Countif formula but cant seem to figure how to have it count all except the "N/A" information.

I´d appreciate the help. 

 

Thank you!

Comments

  • You could use COUNTIFS with OR to count everything that is either a date or text, and NOT to not count the "N/A" cells:

    =COUNTIFS([Column Name]:[Column Name], OR(ISDATE(@cell), ISTEXT(@cell)), [Column Name]:[Column Name], NOT(@cell = "N/A"))

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

    Hi Elena,

    Try something like this.

    This formula might work but it depends on what is included in your column.

    =COUNTIF([Column Name]:[Column Name]; <>"N/A")

    The same version but with the below changes for your and others convenience.

    =COUNTIF([Column Name]:[Column Name], <>"N/A")

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Would that work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • I want to count all rows with the [assigned to] person except where the [customer declined] box is checked.

    This formula gives me an "Invalid Operation"

    =COUNTIFS({520.07db Qualified Testing Facility Databa Range 1}, [Master Planner]@row, NOT({520.07db Qualified Testing Facility Datab Range 11} = 1))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 04/16/20

    @Pam Grant

    Hi Pam,

    You just need to change the end of your formula to:

    =COUNTIFS({520.07db Qualified Testing Facility Databa Range 1}, [Master Planner]@row, NOT({520.07db Qualified Testing Facility Datab Range 11},0)

    Did that work?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as 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: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.