Home 
Search 
Today's Posts 
#1




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? 
#2




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 
#3




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 
#4




SUMIFS with multiple criteria provides wrong result
Thank you Claus. Your followup 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? 
#5




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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
SUMIF (or SUMIFS): Can I have multiple EITHER OR criteria  Excel Worksheet Functions  
SUMIFS, one criteria range, multiple criteria  Excel Worksheet Functions  
New 2007 Sumifs formula  is there a new multiple criteria vlookup  Excel Worksheet Functions  
using sumifs with multiple criteria?  Excel Discussion (Misc queries)  
Return result from multiple criteria  Excel Worksheet Functions 