The COUNTBLANK worksheet function provides the most
convenient method for determining the number of blank cells in a range, but in
two circumstances it is not satisfactory. Those situations are when the cells
of interest are in a closed workbook or when they do not form a contiguous
range. This article shows you how to use a SUM(IF()) array formula in those
The COUNTBLANK worksheet function determines the number of
blank cells within an identified range, but for it to do so, the range of cells
must be contiguous and in an open work book.
To use the COUNTBLANK
function, follow these steps:
- Start Microsoft Excel and open a new worksheet.
- Enter the following data in cells A1:G1
David <blank> Carol <blank> Meng <blank> Helge
- Enter the following data in cells A3:G3
<blank> Johnson <blank> Philips <blank> Phua <blank>
- Give the range A1:G1 the name
RangeOne and the range A3:G3 the name
- To count the blank cells in RangeOne, enter the following
in cells A5:B5
- Save the workbook with the name
COUNTBLANK is will produce correct results when used between
workbooks, but both workbooks must be open. To do this, follow these steps:
- Without closing TestBook1, open a new workbook.
- In cell A1 enter CountBlank and in
cell B1, enter =COUNTBLANK(TestBook1.xls!RangeOne) and
notice that the correct answer is returned.
Note In Excel 2007/2010, you can type =COUNTBLANK(TestBook1.xlsx!RangeOne) because a standard Excel file has an extension .xlsx.
- Save the new workbook as
- On the Window menu, click TestBook1.xls.
Note In Excel 2007/2010, we must click Switch Windows in the Windows group on the View tab and then click TestBook1.xlsx.
- Close TestBook1, returning to TestBook2.
- On the Edit menu, click Links, then click Update Values, and then click Close. In Excel 2007, click Edit Links in the Connections group on the Data menu tab, click Update Values, and then click Close.
Notice the error message #VALUE! in cell B1.
- Reopen TestBook1 then return to TestBook2, if necessary
update the links, and notice that the correct value is again displayed in cell
A similar difficulty occurs if the blank cells you wish to
count are found in non-contiguous ranges. Unlike other functions such as SUM,
COUNTBLANK will accept only one range argument, so a different approach is
necessary. In both of these circumstances you can combine the SUM and IF
functions in an array formula to obtain the result you need.NOTE
: When entering array formulas, you must press CTRL+SHIFT+ENTER
simultaneously rather than simply pressing ENTER.
To use the
SUM(IF()) array formula, follow these steps:
- Open the workbook TestBook2 and in cell A3, enter
- In cell B3, enter
=SUM(IF(ISBLANK(TestBook1.xls!RangeOne),1,0)). Be sure
to press CTRL+SHIFT+ENTER as you complete the entry to create an array
Note In Excel 2007/2010, you can type =SUM(IF(ISBLANK(TestBook1.xlsx!RangeOne),1,0)) because a standard Excel file has an extension .xlsx.
- Close TestBook1 and again update the links.
Notice that while the COUNTBLANK function again returns an error, the SUM(IF())
expression continues to display the correct value.
The SUM(IF()) expression can also be used to deal with the
requirement to count blank cells in non-contiguous ranges. To do that, follow
- Open TestBook1 and in cell A6 enter
- In cell B6, enter
Notice that the correct number of blank cells is displayed.
In the array formula, the plus (+) sign indicates that the two
IF expressions are to be combined in a logical OR operation. If an AND
operation is required, you would use the asterisk (*) instead.
For additional information about combining SUM and IF
functions, click the article number below to view the article in the Microsoft
How to Use a Logical AND or OR in a SUM+IF Statement in Excel
Article ID: 274537 - Last Review: September 18, 2011 - Revision: 6.0
- Microsoft Office Excel 2007
- Microsoft Excel 2002 Standard Edition
- Microsoft Excel 2000 Standard Edition
- Microsoft Office Excel 2003
- Microsoft Excel 2010