XL: Some Worksheet Functions Do Not Allow Array Constants

Article translations Article translations
Article ID: 214286 - View products that this article applies to.
This article was previously published under Q214286
For a Microsoft Excel 98 Macintosh Edition version of this article, see 192222.
Expand all | Collapse all

On This Page

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})

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

Properties

Article ID: 214286 - Last Review: February 2, 2012 - Revision: 2.0
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
Keywords: 
kberrmsg kbnofix kbprb KB214286

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com