Microsoft KB Archive/124216

{|
 * width="100%"|

XL: Some Worksheet Functions Don't Allow Array Constants

 * }

-

The information in this article applies to:


 * 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, version 7.0
 * Microsoft Excel 97 for Windows

-

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.

For example, you receive an error message if you use this function as in the following example:

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

CAUSE
This 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.

WORKAROUND
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}))" NOTE: You must enter this formula as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.

Additional query words: 97 7.0 5.0 XL97

Keywords : kbualink97 kbdta xlformula

Version : MACINTOSH:5.0; WINDOWS:5.0,5.0c,97; Win95:7.0; winnt:5.0

Platform : MACINTOSH Win95 WINDOWS winnt

Issue type : kbprb

Technology :