XL: When to Use SUM(IF()) Instead of COUNTBLANK() (274537)

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 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.
3. Save the new workbook as TestBook2.
4. On the Window menu, click TestBook1.xls.
5. Close TestBook1, returning to TestBook2.
6. On the Edit menu, click Links, then click Update Now, and then click OK.
   
   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.
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.