Difference between revisions of "Documentation/How Tos/Calc: COUNTBLANK function"
From Apache OpenOffice Wiki
< Documentation | How Tos
(refining the content) |
|||
| Line 7: | Line 7: | ||
<tt>'''COUNTBLANK(range)'''</tt> | <tt>'''COUNTBLANK(range)'''</tt> | ||
| − | Returns the number of empty cells in the cell range <tt>'''range'''</tt>. | + | : Returns the number of empty cells in the cell range <tt>'''range'''</tt>. |
| + | |||
| + | : A cell that contains blank text such as spaces, or even text with zero length such as returned by <tt>'''=""'''</tt>, is '''not''' considered empty, even though it may appear empty. | ||
=== Example: === | === Example: === | ||
| Line 13: | Line 15: | ||
<tt>'''COUNTBLANK(A1:B2)'''</tt> | <tt>'''COUNTBLANK(A1:B2)'''</tt> | ||
: returns <tt>'''4'''</tt> if cells A1, A2, B1 and B2 are all empty. | : returns <tt>'''4'''</tt> if cells A1, A2, B1 and B2 are all empty. | ||
| + | |||
| + | <i>Advanced topic:</i> | ||
| + | |||
| + | <tt>'''SUMPRODUCT(TRIM(CLEAN(A1:B2))="")'''</tt> | ||
| + | : returns the count of cells that appear blank - that is, that are really empty or that contain blank text or zero length text. | ||
| + | |||
| + | <tt>'''SUMPRODUCT(ISTEXT(A1:B2);TRIM(CLEAN(A1:B2))="")'''</tt> | ||
| + | : returns the count of cells containing blank text and zero length text. | ||
=== See also: === | === See also: === | ||
| Line 18: | Line 28: | ||
'''[[Documentation/How_Tos/Calc: COUNTA function|COUNTA]]''', | '''[[Documentation/How_Tos/Calc: COUNTA function|COUNTA]]''', | ||
'''[[Documentation/How_Tos/Calc: COUNTIF function|COUNTIF]]''', | '''[[Documentation/How_Tos/Calc: COUNTIF function|COUNTIF]]''', | ||
| − | '''[[Documentation/How_Tos/Calc: ISBLANK function|ISBLANK]]''', | + | '''[[Documentation/How_Tos/Calc: ISBLANK function|ISBLANK]]''' |
| + | |||
| + | '''[[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT]]''', | ||
| + | '''[[Documentation/How_Tos/Calc: TRIM function|TRIM]]''', | ||
| + | '''[[Documentation/How_Tos/Calc: CLEAN function|CLEAN]]''', | ||
| + | '''[[Documentation/How_Tos/Calc: ISTEXT function|ISTEXT]]''' | ||
'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' | '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' | ||
| + | |||
| + | '''[[Documentation/How_Tos/Calc: Mathematical functions|Mathematical functions]]''' | ||
| + | |||
[[fr:Documentation/FR/Calc:Fonction NB.VIDE]] | [[fr:Documentation/FR/Calc:Fonction NB.VIDE]] | ||
Revision as of 17:58, 7 May 2008
COUNTBLANK
Returns the number of empty cells.
Syntax:
COUNTBLANK(range)
- Returns the number of empty cells in the cell range range.
- A cell that contains blank text such as spaces, or even text with zero length such as returned by ="", is not considered empty, even though it may appear empty.
Example:
COUNTBLANK(A1:B2)
- returns 4 if cells A1, A2, B1 and B2 are all empty.
Advanced topic:
SUMPRODUCT(TRIM(CLEAN(A1:B2))="")
- returns the count of cells that appear blank - that is, that are really empty or that contain blank text or zero length text.
SUMPRODUCT(ISTEXT(A1:B2);TRIM(CLEAN(A1:B2))="")
- returns the count of cells containing blank text and zero length text.
See also:
COUNT, COUNTA, COUNTIF, ISBLANK
SUMPRODUCT, TRIM, CLEAN, ISTEXT
