Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-26-2013, 03:05 PM
SteveBump SteveBump is offline SUMIF results changed based on filters Windows 7 64bit SUMIF results changed based on filters Office 2010 64bit
Novice
SUMIF results changed based on filters
 
Join Date: Oct 2013
Posts: 24
SteveBump is on a distinguished road
Default SUMIF results changed based on filters

I have a pair of simple SUMIF statements that check for values in another sheet. One cell sums values from the other sheet that are currency and a cell right next to it sums values that are whole numbers (not currency). The criteria is the same for both formulas.

When I go to the sheet where the values to be summed exist, the results for the whole numbers calculation changes based on what is filtered (row 1). I can't narrow it down to what gets included and excluded. All I know is when nothing is filtered, I get the correct results. But if I go and filter any column, not even one in the formula, I get different results. Only the whole number SUMIF results change. The summed dollar results stay the same regardless of what is filtered. It seems like it should work the way the dollar SUMIF is working.

Is this a bug? Is there a way to get around it?
Reply With Quote
  #2  
Old 10-27-2013, 01:50 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline SUMIF results changed based on filters Windows 7 64bit SUMIF results changed based on filters Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Hi and welcome

perhaps post a sample sheet stating BEFORE and AFTER ?

( you'll have to try harder than that to find a bug in XL, although there are some )
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 10-28-2013, 10:48 AM
SteveBump SteveBump is offline SUMIF results changed based on filters Windows 7 64bit SUMIF results changed based on filters Office 2010 64bit
Novice
SUMIF results changed based on filters
 
Join Date: Oct 2013
Posts: 24
SteveBump is on a distinguished road
Default SUMIF / SUBTOTAL issue (with attachment)

As requested, I am submitting a spreadsheet that reproduces the problem. If you go to Sheet2, you will see two SUMIF computations. One is summing values in Sheet1 that includes a "Subtotal" calculation. The other sums raw whole numbers. There are four rows in Sheet1; two labeled "App1" and two labeled "App2".
If you filter on either choice in Sheet1, the results in the SUMIF statement on Sheet2 for the Computed Number change to an incorrect value.
The problem appears to be with the values in Sheet1, column E, which is computed with the statement =SUBTOTAL(9,F2:G2). When I change all of the SUBTOTAL statements to SUM(F2:G2), it works properly when I filter for either App1 or App2.
Is this a bug or am I misunderstanding how it works.
Attached Files
File Type: xlsx Possible SUMIF Bug.xlsx (17.9 KB, 9 views)
Reply With Quote
  #4  
Old 10-29-2013, 05:32 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline SUMIF results changed based on filters Windows 7 64bit SUMIF results changed based on filters Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

  • Quote:
    The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.
This is what MS has to say about it in it's description of the function.
So, again, no new bug discovered
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 10-29-2013, 06:08 AM
SteveBump SteveBump is offline SUMIF results changed based on filters Windows 7 64bit SUMIF results changed based on filters Office 2010 64bit
Novice
SUMIF results changed based on filters
 
Join Date: Oct 2013
Posts: 24
SteveBump is on a distinguished road
Default

Thanks. That makes sense. But I am disappointed in how Excel created the formula using "SUBTOTAL" when I hit the SUM button on the toolbar. That was not entered by me. It was only when the results started changing that I tracked it down and discovered the use of SUBTOTAL. Thanks again.
Reply With Quote
  #6  
Old 10-29-2013, 07:25 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline SUMIF results changed based on filters Windows 7 64bit SUMIF results changed based on filters Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

One way or another we both learned something !
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Tags
sumif bug filters

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
2013 search results take a long time - they fill in as results in reverse date order themookman Outlook 0 10-11-2013 12:01 PM
Help! 'SUMIF' Formulas dave1372 Excel 2 09-08-2011 07:05 AM
SUMIF results changed based on filters SUMIF Formula question jcaswell Excel 3 05-22-2011 02:52 AM
SUMIF results changed based on filters Need Help With SUMIF Formula In R1C1 Style Todd Excel 9 02-27-2010 08:30 PM
SUMIF results changed based on filters Multiple criteria in SUMIF? pumpkin head Excel 1 02-17-2006 09:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:43 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft