Excel Forum
Similar Content
Multiply by True and False in Excel
Tutorial: You can multiply numbers by TRUE and FALSE in Excel. This effectively allows you to elimi...
Easily Input Complex Functions in Excel
Tutorial: In this tutorial I am going to show you how to easily input complex Functions in Excel. To...
Convert Numbers into True and False in Excel
Tutorial: How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very impor...

Excel Magic Trick 315: SUMIF Only Accept Ranges, Not Arrays

Learn about the limitations of the SUMIF function it cannot accept arrays, only ranges. This means you must use other means like the SUMPRODUCT function.

Mr Excel Message Board post from barry houdini:

Point #1: SUMIF, and the like, only accept ranges of cells, not arrays

Point #2: Functions such as YEAR, TEXT that are expecting a single value and are given a range (except for LOOKUP and SUMPRODUCT) become arrays if the argument is given a range

Point#3: A range extracted from a closed workbook becomes an array and thus for a SUMIF function yields a #VALUE error. To fix this use the INDIRECT function, or just use SUMPRODUCT.

SUM with Multiple Criteria
SUM with more than 1 Criteria

SUMIF to another workbook #VALUE! Error because range gets converted to an array.

Got a Question? Ask it Here in the Forum.