COUNTIFS Not Blank

A Rose · 07/01/21

Hi,

How can I create a formula, count if "Column 1" conatians "value 1", anc "Column 2" contains any value?

Thank you!

A Rose · 07/02/21

https://community.smartsheet.com/discussion/comment/296807#Comment_296807

Now I got it,

thanks! 😀

Toufong Vang · 07/02/21

<> is the expression for "not equal to", so <>"" is read as "not equal to BLANK". If it does not matter what is in the cell, that is to say "any value", then the way to represent that is <>"".The alternative is something specific or one of many possible values.

You can also use the combination of ISBLANK() and NOT() to evaluate if "any value" is in the cell. For example, ISBLANK([Column 2]@row) will check to see if the cell is blank. Enclosing that statement within NOT(), e.g, NOT(ISBLANK([Column 2]@row)), essentially means "[Column 2]@row is not blank".

You can nest functions within other functions so long as you abide by the function's syntax (structure).

Toufong Vang · 07/01/21

You already have! 😀

You identified:

The function you need, COUNTIFS().
The range to count, "Column 1", which is expressed as [Column 1]:[Column 1].
The first criterion, "contains value 1" , for which there is the function CONTAINS().
The second range, "Column 2", which is expressed as [Column 2]:[Column 2].
The criterion for the second range, "any value"--which also means "not blank or empty". One way to represent "not blank or empty" is <>"" .

Now put it all together...

COUNTIFS() syntax is COUNTIFS( range1, criterion1, range2, criterion2, [range3, criterion3, etc...] )

COUNTIFS( [Column 1]:[Column 1], CONTAINS( "value 1", @cell)=true, [Column 2]:[Column 2], <>"")

Done.

One way to think of "@cell" is to imagine..."while working through a range of cells one-by-one, evaluate at the current/present cell..." In this case, you're checking to see if whatever is in there contains "value 1"--CONTAINS() returns "true" if found, otherwise, "false".

You can find a list of Smartsheet functions and how to use them on this page, https://help.smartsheet.com/functions.

Hope you found this helpful.

A Rose · 07/02/21

Hi @Toufong Vang ,

Thanks for your help!

I need something similar, I need the same formula but second value should be any value,

is that the reason for the symbol <> ?

Thanks 😊

A Rose · 07/02/21

https://community.smartsheet.com/discussion/comment/296807#Comment_296807

Now I got it,

thanks! 😀

Toufong Vang · 07/02/21

<> is the expression for "not equal to", so <>"" is read as "not equal to BLANK". If it does not matter what is in the cell, that is to say "any value", then the way to represent that is <>"".The alternative is something specific or one of many possible values.

You can also use the combination of ISBLANK() and NOT() to evaluate if "any value" is in the cell. For example, ISBLANK([Column 2]@row) will check to see if the cell is blank. Enclosing that statement within NOT(), e.g, NOT(ISBLANK([Column 2]@row)), essentially means "[Column 2]@row is not blank".

You can nest functions within other functions so long as you abide by the function's syntax (structure).

COUNTIFS Not Blank

Best Answers

Answers

Help Article Resources

Categories