Excel Forum

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.