Article ID: 214286
Article Last Modified on 1/24/2007
APPLIES TO
- Microsoft Excel 2000 Standard Edition
- Microsoft Excel 2002 Standard Edition
- Microsoft Excel 97 Standard Edition
This article was previously published under Q214286
For a Microsoft Excel 98 Macintosh Edition version of this article, see 192222.
SYMPTOMS
In Microsoft Excel, 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:
-or-
For example, you receive an error message if you use this function in the manner of the following example:
=SUMIF({1,2},2,{1,1})
CAUSE
This behavior occurs because the SUMIF(), COUNTIF(), and COUNTBLANK() functions use the same criteria-matching algorithm as database functions, such as DSUM(). This algorithm does not support arrays.
WORKAROUND
To work around this behavior, 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.
Additional query words: XL2000 xl2002
Keywords: kberrmsg kbnofix kbprb KB214286
