# XL: Some Worksheet Functions Do Not Allow Array Constants

Article translations
Close
For a Microsoft Excel 98 Macintosh Edition version of this article, see 192222.
Expand all | Collapse all

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