When to use SUM(IF()) instead of COUNTBLANK() in Excel

Applies to: Microsoft Office Excel 2007Microsoft Office Excel 2003Excel 2010


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

More Information

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:
  1. Start Microsoft Excel and open a new worksheet.
  2. Enter the following data in cells A1:G1

    David <blank> Carol <blank> Meng <blank> Helge
  3. Enter the following data in cells A3:G3

    <blank> Johnson <blank> Philips <blank> Phua <blank>
  4. Give the range A1:G1 the name
    RangeOne and the range A3:G3 the name
  5. To count the blank cells in RangeOne, enter the following in cells A5:B5

    Blanks =COUNTBLANK(RangeOne)
  6. 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:
  1. Without closing TestBook1, open a new workbook.
  2. 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.
  3. Save the new workbook as
  4. 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.
  5. Close TestBook1, returning to TestBook2.
  6. 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.
  7. Reopen TestBook1 then return to TestBook2, if necessary update the links, and notice that the correct value is again displayed in cell B1.
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:
  1. Open the workbook TestBook2 and in cell A3, enter
  2. 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 formula.

    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.
  3. 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 these steps:
  1. Open TestBook1 and in cell A6 enter
  2. 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 Knowledge Base:
267982 How to Use a Logical AND or OR in a SUM+IF Statement in Excel