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?

SUMIFS with multiple criteria provides wrong result
 

Hi Gijs,

Am Tue, 24 Oct 2017 11:15:51 +0100 schrieb GijsKijlstra:

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

what about columns G and H? Do you only want to summarize if G /AND/ H
are the same as the criteria?
If you want to summerize if G or H are the same as the criteria, try:

=SUMPRODUCT((Input!A1:A100=$I$2)*(Input!A1:A100<= EDATE($I$2,1))*(Input!G1:H100=$G$2)*(Input!B1:B100 =$G$16)*Input!F1:F100)


Regards
Claus B.
--
Windows10
Office 2016

SUMIFS with multiple criteria provides wrong result
 

Hi again,

Am Tue, 24 Oct 2017 13:32:12 +0200 schrieb Claus Busch:

=SUMPRODUCT((Input!A1:A100=$I$2)*(Input!A1:A100<= EDATE($I$2,1))*(Input!G1:H100=$G$2)*(Input!B1:B100 =$G$16)*Input!F1:F100)

if you don't want to summarize F twice if also G and H match the
criteria, try:
=SUM(IF((Input!G1:G100=G2)+(Input!H1:H100=G2),(Inp ut!B1:B100=G16)*(Input!A1:A100=I2)*(Input!A1:A100 <=EDATE(I2,1))*Input!F1:F100))
and insert this formula with CTRL+Shift+Enter


Regards
Claus B.
--
Windows10
Office 2016

SUMIFS with multiple criteria provides wrong result
 

Thank you Claus. Your follow-up response as well, is greatly appreciated. I had to modify your (1st) solution to avoid a #n/a result:
=SUMPRODUCT((Input!$A$6:$A$100000=I$2)*
(Input!$A$6:$A$100000<= EDATE(I$2,1))*
(Input!$G$6:$H$100000=$G$2)*
(Input!$B$6:$B$100000=$G9)*
Input!$F$6:$F$100000)

Regrettably, after the modification, the result isn’t correct. In the event you have time, let me clarify my requirement:

The only difference between column Input!B and the columns Input!G+H is that Input!B’s range is one column and Input!G+H are separated into two columns. The qualifying role of Input!B and Input!G+H are identical (I can’t merge Input!G and Input!H into one column).

Column Input!G has its own elements and will NOT appear in column Input!H that has its own elements as well and do NOT appear in Input!G.
($G$2) determines the criteria that could be found in the elements in Input!G OR in Input!H. In other words: the determining cell ($G$2) will only be found in Input!B OR Input!G+H.

If I were to do the calculation manually, I would do it as follows:
When the date (Input!$A6:$A100000) matches the month (I$2) AND
The range1 Input!$G6:$G100000) matches criteria1 ($G$2) OR
The range2 Input!$H6:$H100000) matches criteria1 ($G$2) AND
The range3 (Input!$B6:$B100000) matches criteria2 ($G9),
Sum the values (Input!$F6:$F100000)

Is this feasible?

SUMIFS with multiple criteria provides wrong result
 

Hi Gijs,

Am Wed, 25 Oct 2017 03:24:13 +0100 schrieb GijsKijlstra:

If I were to do the calculation manually, I would do it as follows:
When the date (Input!$A6:$A100000) matches the month (I$2) AND
The range1 Input!$G6:$G100000) matches criteria1 ($G$2) OR
The range2 Input!$H6:$H100000) matches criteria1 ($G$2) AND
The range3 (Input!$B6:$B100000) matches criteria2 ($G9),
Sum the values (Input!$F6:$F100000)

then try:
=SUMMENPRODUKT((Input!A1:A100=$I$2)*(Input!A1:A10 0<=EDATUM($I$2;1))*((Input!G1:G100=G2)+(Input!H1:H 100=G2)+(Input!B1:B100=$G$16))*Input!F1:F100)


Regards
Claus B.
--
Windows10
Office 2016

Vielen Dank. Ein schönes Wochenende