XL: Some Worksheet Functions Do Not Allow Array Constants

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: -or- For example, you receive an error message if you use this function in the manner of the following example:

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.

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:

Method 2

To use the SUM(IF()) function to enter the array, use a formula similar to the following: 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.