#1
|
|||
|
|||
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? |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
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.
|
#6
|
||||
|
||||
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 |
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 Formula question | jcaswell | Excel | 3 | 05-22-2011 02:52 AM |
Need Help With SUMIF Formula In R1C1 Style | Todd | Excel | 9 | 02-27-2010 08:30 PM |
Multiple criteria in SUMIF? | pumpkin head | Excel | 1 | 02-17-2006 09:06 AM |