Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Counting a cell with any data present

Options
Alethea Carbaugh
edited 12/09/19 in Archived 2017 Posts

I have two questions here, any and all help would be greatly appreciated.  A little background might be useful.  Rows in my smartsheet are all considered one video item.  A variety of things are tracked in these rows via the columns.  

 

1. Using formulas (specifically COUNTIF OR COUNTIFS), can I count a row that has two columns with the same specific details?  One row has a specific status (I'm able to count this "=COUNTIF([PRODUCTION STATUS]:[PRODUCTION STATUS], "SCHEDULED")"  I'd like to add to this count formula another column (EMBARGO DATE) and I would like the row to be counted if there is ANY information in that column (normally the information is a date). I've tried =COUNTIFS([PRODUCTION STATUS]:[PRODUCTION STATUS], "SCHEDULED", [EMBARGO DATE]:[EMBARGO DATE], "*") but the equasion comes up #UNPARSEABLE.  Is there anyway to just count a cell in a column that is NOT BLANK?  If that was the case, I could just count both columns sepearately an compare the two numbers. 

 

2. Is there anyway to count two different statuses in one column?   Basically I'd like to use COUNTIF this status OR this status exists.  Is that possible?

 

Thanks for your help community! 

Alethea 

Comments

  • Shaine Greenwood
    Options

    Hi Alethea,

     

    1. I was able to get this to work:

    =COUNTIF([Task Name]:[Task Name], NOT(ISBLANK(@cell)))

     

    Change the column range reference from my example to the column you're wanting to count in your own sheet. You can find more information on our functions in the Help Center: https://help.smartsheet.com/articles/775363-using-formulas

     

    2. You should be able to make a +COUNTIF(OR()) function, like the following example:

    =COUNTIF([Task Name]:[Task Name], OR(@cell = "Red", @cell = "Task 1"))

     

    The above example goes through a column titled "Task Name" and will count each cell that contains the text string "Red" OR the text string "Task 1"

     

    Hope this helps!

  • jacquestelles
    Options

    Shaine

    I'm working on a similar challenge trying to put some better logic in my script for counting data in columns and reporting status and counts. There's two parts I'm trying to address.

    part 1 - count the  occurrence of workstream  name in column called "IT Track". This was the original code I inherited and it didnt work well when teams deleted rows from the sheet. =COUNTIFS($[IT Track]$15:$[IT Track]$1081, "02. Supply Chain"). Note: My summary table occupies rows 1 to 14. I changed it to this =COUNTIFS([IT Track]:[IT Track], OR(@cell = "02. Supply Chain")) and it looks good.

    Part 2 - I want to build off my =COUNTIFS([IT Track]:[IT Track], OR(@cell = "02. Supply Chain")) statement and now count the number when Column "Tier" = Tier 1 and Column "Status" = Green. How do I nest these statements so that when all criteria is met, I get an accurate count?

    =COUNTIFS([IT Track]:[IT Track], OR(@cell = "02. Supply Chain"))

    =COUNTIFS([Tier]:[Tier], OR(@cell = "Tier1"))

    =COUNTIFS([Status]:[Status], OR(@cell = "Green"))

    Here's the original statement =COUNTIFS($[IT Track]$15:$[IT Track]$1081, "02. Supply Chain", $Tier$15:$Tier1081, "Tier 1", $Status$15:$Status$1081, "Green")

     

    Thanks

    Jacques

  • jacquestelles
    Options

    I tried this path with my script but I get UNPARSEABLE error message

    =COUNTIFS(Status:Status, = "Green", Tier:Tier, "Tier 1", [IT Track]:[IT Track], "02. Supply Chain”)

  • jacquestelles
    edited 03/01/18
    Options

    I built it in stages and got to this piece in my script =COUNTIFS(Status:Status, ="Green", Tier:Tier, "Tier 1") with success...

    When I add [IT Track]:[IT Track], "02. Supply Chain” to the script I get the error message... suggestions?

  • Shaine Greenwood
    Options

    Hi Jacques,

    Your original formula has an extraneous equal sign next to green, try removing it.

    =COUNTIFS(Status:Status, "Green", Tier:Tier, "Tier 1", [IT Track]:[IT Track], "02. Supply Chain")

    If you get an error from the above formula, let me know the specific error message and I can further assist.

  • jacquestelles
    Options

    Shaine

    SUCCESS!!! thank you!

     

    Jacques

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Glad you got it to work, but that equal sign was not the problem.

    This works.

    =COUNTIFS(Status:Status, ="Green", Tier:Tier, "Tier 1", [IT Track]:[IT Track], "02. Supply Chain")

    So do an additional space ,= "Green",

    The error in your example:

    =COUNTIFS(Status:Status, = "Green", Tier:Tier, "Tier 1", [IT Track]:[IT Track], "02. Supply Chain”)

    is the last end quote.

    This:

    is not allowed.

    This:

    "

    is

    (curved double quote ('smart quotes') vs straight double quote.)

    MS Office products change straight to 'smart' by default, so that may be where the error originated.

    Craig

  • Shaine Greenwood
    Shaine Greenwood Employee
    edited 03/01/18
    Options

    Just noticed the curly quote on jacques' formula, that was likely one of the culprits. Although I still receive #UNPARSEABLE  when using: =COUNTIFS(Status:Status,="Green", Tier:Tier, "Tier 1", [IT Track]:[IT Track], "02. Supply Chain")

    The extra equals, from my understanding, isn't just unneeded, it causes the #UNPARSEABLE error. Not sure how you got that formula to work in your sheet, Craig.

  • jacquestelles
    edited 03/01/18
    Options

    Got it! I'll need to watch those... I think coping and pasting my source and using MS word to play with the syntax might be generating the wrong quote marks! 

This discussion has been closed.