XL: Some Worksheet Functions Don't Allow Array Constants

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, version 5.0
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, you may receive one of the following error messages if you insert an array constant (such as {1,2}) into a SUMIF(), COUNTIF(), or COUNTBLANK() function:

   Error in formula.

   -or-

   The formula you typed contains an error.

   =SUMIF({1,2},2,{1,1})

CAUSE

The problem occurs because the SUMIF(), COUNTIF(), and COUNTBLANK() functions use the same criteria-matching algorithm as the database functions, such as DSUM(). This algorithm does not support arrays.

WORKAROUNDS

To work around this problem, use either of the following methods to enter the array.

Method 1

To use the constants in a range reference, use a formula similar to the following:

   =SUMIF(A1:A2,2,B1:B2)

Method 2

To use the SUM(IF()) function to enter the array, use a formula similar to the following:

   =SUM(IF({1,2}=2,{1,1}))

STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.