Results 1 to 15 of 16
Thread: Formula problem

20140517, 01:55 #1
 Join Date
 Feb 2008
 Posts
 945
 Thanks
 62
 Thanked 2 Times in 2 Posts
Formula problem
I am struggling to get the individual account subtotals to display correctly on sheet "Data"
The concept is that :
If My filter hides the 1st occurrence, then the subtotal should appear on first VISIBLE row
The workbook should always show the TOTAL of the imported data (I2), but if the workbook is filtered, it should also show the filtered total in row 3
Even though the subtotals add up to the correct total when added manually – my subtotal go out of sync
It seems to work if I filter out individual entries, even if I change the sort order, but as soon as I filter out 0 values an incorrect value is returned for some reason & gets even crazier if I sort these filtered values
Your assistance in providing a solution in this regard is most appreciated

Subscribe to our Windows Secrets Newsletter  It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20140517, 04:08 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,161
 Thanks
 14
 Thanked 317 Times in 311 Posts
Could you walk me through step by step what to do with the example file to generate an incorrect response and tell us what the correct response should be and the logic behind the correct response. You seem to be talking about a filtering issue, but there are also several macros in the workbook as well.
Steve

20140517, 04:40 #3
 Join Date
 Feb 2008
 Posts
 945
 Thanks
 62
 Thanked 2 Times in 2 Posts
Hi Steve
Will get back to you. One of my friends emailed me the file and asked me to help, but not familiar with some of the formulas for eg Sumproduct (have used this more than a year ago)
Will get step by step explanation and get back to you
Howard

20140517, 07:44 #4
 Join Date
 Feb 2008
 Posts
 945
 Thanks
 62
 Thanked 2 Times in 2 Posts
Steve
If All The items are filtered , then the formulas In I2 to L4 gives the correct answer. However, If I deselect a value by clicking on the drop down filter in J5 and deselect for eg R 39505.51 , the formulas in I2:L4 returns an incorrect result
Please test and amend formula so that if I filter the data, I still get the correct value

20140517, 08:53 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,161
 Thanks
 14
 Thanked 317 Times in 311 Posts
I3 seems to give the correct result. I2 has the complete total 22,443.66. If I remove the value 39,505.51 the filtered total in I3 gives +17,061.85 which is the difference in the numbers. The values in J3:L3 give the exact same values as the J2:L2 since they each column has the same formula. If you want the filtered value you need to copy the formual from I3 to J3:L3.
Note that this may still not be what you want since some of your formulas in the table use the SUBTOTAL function. Not only will subtotal ignore data that is removed by a filter, it will (by design) ignore the values from another subtotal function. Values in the col J do not use subtotals, but the values in K and L do...
Steve

20140517, 09:31 #6
 Join Date
 Feb 2008
 Posts
 945
 Thanks
 62
 Thanked 2 Times in 2 Posts
I have copied to Formula from I3 to J3:L# and when I remove the Value 39505.51, I get the corrected Filtered Totals in I3 & J3, but the filtered totals in K3:L3 is incorrect as well as the total in J2:L2
It would be appreciated if you could provide a formula to resolve this problem

20140517, 11:34 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,161
 Thanks
 14
 Thanked 317 Times in 311 Posts
You will have to be more specific about what you want to accomplish and the logic. As I mentioned in my previous post K3 & L3 give the values I expect them to be since the values in rows 6, 26, 28, 39, 73, and 82 in those columns contain subtotals and are ignored. You will have to create formulas similar to those in cols I & J if in the data rows you do not want those values ignored. Or if that is not an option, you would have to create a user defined function that mimics some, but not all of the subtotal formula to put in row 2. If you want help with a UDF you will have to be explicit about exactly you need it to and what the input will be.
As to the totals in J2:L2, I have no idea what you expect the results to be. I did not offer a solution to them, but I thought you wanted them to be the sums of those columns (and they but they match the conventional sums in row 4).
Please explain in detail what you want the formulas to obtain and the logic, if you want help resolving the issue.
Steve

20140517, 15:46 #8
 Join Date
 Feb 2008
 Posts
 945
 Thanks
 62
 Thanked 2 Times in 2 Posts
Steve
I will come back to you, once I have clarification from my friend. However, the filtered values in K3 & L3 should all be (22,443.66) per the attached sample data
It would be appreciated if you would test & advise
Howard

20140518, 05:22 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,161
 Thanks
 14
 Thanked 317 Times in 311 Posts
You don't seem to be answering the questions I raise or do not understand the issues and the spreadsheet you are asking about. I will presume that you do NOT want to change the formulas in the datarows that have subtotals, which means that you can NOT solve the issue with a builtin excel formula. Here is a UDF, add it to a module:
Code:Option Explicit Function SumVisible(rng As Range) Dim rCell As Range Dim dSubTotal As Double Application.Volatile dSubTotal = 0 For Each rCell In rng If IsNumeric(rCell) And Not rCell.EntireRow.Hidden Then dSubTotal = dSubTotal + rCell.Value End If Next SumVisible = dSubTotal End Function
=SumVisible(OFFSET(K5,1,0,5000))
copy this to L3
Steve

20140518, 05:33 #10
 Join Date
 Feb 2008
 Posts
 945
 Thanks
 62
 Thanked 2 Times in 2 Posts
Hi Steve
My friend advised that the formulas in I2:I3 gives the correct answers with any value EXCEPT for when “<>0” filter applied
It would be appreciated if you could assist in resolving this
Howard

20140518, 05:34 #11
 Join Date
 Feb 2008
 Posts
 945
 Thanks
 62
 Thanked 2 Times in 2 Posts
Thanks Steve will try this
Howard

20140518, 05:57 #12
 Join Date
 Feb 2008
 Posts
 945
 Thanks
 62
 Thanked 2 Times in 2 Posts
Steve
My apologies for not answering your questions as I was also trying to understand the logic and my friend was not being clear in his replies to me
To me it appears that the UDF is working 100%. Will revert back to you if not working
Howard

20140518, 07:33 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,161
 Thanks
 14
 Thanked 317 Times in 311 Posts
It seems to be what I expect in col I. With or without the <>0 it is the same value, since a zero does not affect the sum.
Col JL seems to be the total and the filtered total accurately. What your friend may not realize is that if <>0 is chosen in Col I, Rows 51  65 (which are all hidden and can not be seen), get the value 45.95 which changes the total in J2:L2 and J4:L4
If that is the issue, the problem is NOT the formulas in J2:L4 which are calculating accurately), but the formulas in the datatable in rows 5165 in those columns
Steve

20140518, 08:28 #14
 Join Date
 Feb 2008
 Posts
 945
 Thanks
 62
 Thanked 2 Times in 2 Posts
Thanks for your valuable input Stevethis is much appreciated
I will bring it to my friends attention regarding the formulas in the datatable in rows 5165 in columns J:L
Howard

20140518, 09:05 #15
 Join Date
 May 2014
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Is there a guide online for all the formula we can use for excel?