Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 24th 17, 11:15 AM
Junior Member
 
First recorded activity by ExcelBanter: Mar 2012
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?

  #2   Report Post  
Old October 24th 17, 12:32 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,730
Default 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   Report Post  
Old October 24th 17, 01:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,730
Default 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   Report Post  
Old October 25th 17, 03:24 AM
Junior Member
 
First recorded activity by ExcelBanter: Mar 2012
Location: Singapore
Posts: 20
Send a message via Skype™ to GijsKijlstra
Default 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?
  #5   Report Post  
Old October 25th 17, 08:27 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,730
Default 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


  #6   Report Post  
Old October 27th 17, 01:48 PM
Junior Member
 
First recorded activity by ExcelBanter: Mar 2012
Location: Singapore
Posts: 20
Send a message via Skype™ to GijsKijlstra
Default

Vielen Dank. Ein schönes Wochenende


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIF (or SUMIFS): Can I have multiple EITHER OR criteria AC Excel Worksheet Functions 1 November 10th 11 03:48 AM
SUMIFS, one criteria range, multiple criteria GavinS Excel Worksheet Functions 2 September 7th 11 05:40 PM
New 2007 Sumifs formula - is there a new multiple criteria vlookup westy Excel Worksheet Functions 2 July 1st 09 06:41 AM
using sumifs with multiple criteria? Celia Excel Discussion (Misc queries) 3 April 8th 09 02:14 AM
Return result from multiple criteria Pat Excel Worksheet Functions 6 December 16th 04 04:39 PM


All times are GMT +1. The time now is 12:00 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017