COUNTIFS Not Blank
Hi,
How can I create a formula, count if "Column 1" conatians "value 1", anc "Column 2" contains any value?
Thank you!
Best Answers
-
Now I got it,
thanks! 😀
-
<>
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).
Answers
-
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.
-
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 😊
-
Now I got it,
thanks! 😀
-
<>
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).
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!