Counting a cell with any data present

Alethea Carbaugh

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 

Shaine Greenwood

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

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

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

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

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

Shaine

SUCCESS!!! thank you!

 

Jacques

J. Craig Williams

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

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

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!