Remember Me?

 GijsKijlstra Junior Member First recorded activity by ExcelBanter: Mar 2012 Location: Singapore Posts: 20 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? October 24th 17, 12:32 PM posted to microsoft.public.excel.worksheet.functions
 Claus Busch external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,787 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 October 24th 17, 01:33 PM posted to microsoft.public.excel.worksheet.functions
 Claus Busch external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,787 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
 GijsKijlstra Junior Member First recorded activity by ExcelBanter: Mar 2012 Location: Singapore Posts: 20 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? October 25th 17, 08:27 AM posted to microsoft.public.excel.worksheet.functions
 Claus Busch external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,787 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

 GijsKijlstra Junior Member First recorded activity by ExcelBanter: Mar 2012 Location: Singapore Posts: 20 Vielen Dank. Ein schönes Wochenende

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post AC Excel Worksheet Functions 1 November 10th 11 02:48 AM GavinS Excel Worksheet Functions 2 September 7th 11 05:40 PM westy Excel Worksheet Functions 2 July 1st 09 06:41 AM Celia Excel Discussion (Misc queries) 3 April 8th 09 02:14 AM Pat Excel Worksheet Functions 6 December 16th 04 03:39 PM

All times are GMT +1. The time now is 11:49 PM. Copyright ©2004-2020 ExcelBanter.