LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
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?
 
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 02: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 03:39 PM


All times are GMT +1. The time now is 08:21 PM.

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

About Us

"It's about Microsoft Excel"