|
|
Featured Tutorial
Featured Macro
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.
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.