Formula

I am new ti Smartsheet and formulas.

This is the formula I have, and it is not working, and I don't know what I have done wrong.

=COUNTIFS({Stage 6}, ="6- Testing", [{Stage 61}, ="6- Opening"])

What I am trying to get is the number of projects that are in phase 6 weather it is opening or testing. Both are values in the same column on the same sheet.

Best Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Give this a try:

    =COUNTIFS({Stage 6}, OR(@cell = "6- Testing", @cell ="6- Opening"))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Your syntax is a bit off to start. You don't need the square brackets around the second range/criteria set.

    =COUNTIFS({Stage 6}, ="6- Testing", {Stage 61}, ="6- Opening")


    I also notice that you said they are both in the same column yet you have two different ranges listed in your formula (6 and 61).


    Even after correcting the syntax and range issue though, it still will not accomplish what you are wanting because the COUNTIFS implies "and" when looking at multiple range/criteria sets. This means you are technically counting rows where the same cell is equal to both "6- Testing" AND "6- Opening" at the same time which is not possible.


    What you really want is to count cells within the same range that are either "6- Testing" OR "6- Opening", and that looks more like:

    =COUNTIFS({Range}, OR(@cell = "6- Testing", @cell = "6- Opening"))

    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

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Give this a try:

    =COUNTIFS({Stage 6}, OR(@cell = "6- Testing", @cell ="6- Opening"))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Your syntax is a bit off to start. You don't need the square brackets around the second range/criteria set.

    =COUNTIFS({Stage 6}, ="6- Testing", {Stage 61}, ="6- Opening")


    I also notice that you said they are both in the same column yet you have two different ranges listed in your formula (6 and 61).


    Even after correcting the syntax and range issue though, it still will not accomplish what you are wanting because the COUNTIFS implies "and" when looking at multiple range/criteria sets. This means you are technically counting rows where the same cell is equal to both "6- Testing" AND "6- Opening" at the same time which is not possible.


    What you really want is to count cells within the same range that are either "6- Testing" OR "6- Opening", and that looks more like:

    =COUNTIFS({Range}, OR(@cell = "6- Testing", @cell = "6- Opening"))

    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!