SUMIFS with multiple criteria provides wrong result
BACKGROUND
In cell i9 I have the following formula:
=SUMIFS(Input!$F:$F,
Input!$A:$A,"="&I$2,
Input!$A:$A,"<"&EDATE(I$2,1),
Input!$G:$G,$G$2,
Input!$H:$H,$G$2,
Input!$B:$B,$G16)
EXPLANATION
Input!$F:$F is a column with values
Input!$A:$A is a column with range1 (dates in format: dd/mm/yyyy)
I$2 is criteria1
Input!$G:$G is a (text) column with range2; $G$2 is (text) criteria2
Input!$H:$H is a (text) column with range3; $G$2 is (text) criteria3
Input!$B:$B is a (text) column with range4; $G9 is (text) criteria4
To do it manually, I would:
In cell i9
sum the values from sheet ‘Input’ column F, based on
(sheet ‘Input’) the range1 month (column A), based on (sheet ‘Per month’) criteria1 (cell i2) and
(sheet ‘Input’) range2 (column G), ), based on (sheet ‘Per month’) criteria2 (cell G2) and
(sheet ‘Input’) range3 (column H), ), based on (sheet ‘Per month’) criteria3 (identical cell G2)
RESULT is a wrong sum
However, when I eliminate range 2 and criteria 2 (Input!$H:$H,$G$2), the formula provides the correct information
How can I correct this formula?
|