XL98: Some Worksheet Functions Do Not Allow Array Constants

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q192222

For a Microsoft Excel 97 and later version of this article, see 192222 (http://support.microsoft.com/kb/192222/EN-US/ ) .

SYMPTOMS

In Microsoft Excel 98 Macintosh Edition, 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})

Back to the top

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.

Back to the top

WORKAROUND

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

Back to the top

Method 1

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

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

Back to the top

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, press COMMAND+RETURN.

Back to the top

APPLIES TO

Microsoft Excel 98 for Macintosh

Back to the top

kbnofix kbprb KB192222

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top