COUNTIFS Not Blank

A Rose

Hi,

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

Thank you!

A Rose

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

Now I got it,

thanks! 😀

Toufong Vang

<> 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

You already have! 😀

You identified:

1. The function you need, COUNTIFS().
2. The range to count, "Column 1", which is expressed as [Column 1]:[Column 1].
3. The first criterion, "contains value 1" , for which there is the function CONTAINS().
4. The second range, "Column 2", which is expressed as [Column 2]:[Column 2].
5. 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

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

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

Now I got it,

thanks! 😀

Toufong Vang

<> 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).