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

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
   RangeTwo.
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
   TestBook1.

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
   TestBook2.
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
   SumIf.
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
   SumIf.
2. In cell B6, enter
   =SUM(IF(ISBLANK(RangeOne),1,0)+IF(ISBLANK(RangeTwo),1,0)).
   
   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.