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

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

## Applies to:

↑ 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