View Single Post
  #1   Report Post  
GijsKijlstra GijsKijlstra is offline
Junior Member
 
Location: Singapore
Posts: 20
Send a message via Skype™ to GijsKijlstra
Default 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?