Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

XL: Some Worksheet Functions Do Not Allow Array Constants


View products that this article applies to.

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:
Error in formula.
-or-
The formula you typed contains an error.
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})

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

↑ Back to the top


Keywords: KB214286, kbprb, kbnofix, kberrmsg

↑ Back to the top

Article Info
Article ID : 214286
Revision : 2
Created on : 2/2/2012
Published on : 2/2/2012
Exists online : False
Views : 420