Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Formula problem

  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    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
    Attached Files Attached Files

  2. 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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,196
    Thanks
    14
    Thanked 326 Times in 320 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

  4. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    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

  5. #4
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    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 de-select a value by clicking on the drop down filter in J5 and de-select 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

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,196
    Thanks
    14
    Thanked 326 Times in 320 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

  7. #6
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    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

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,196
    Thanks
    14
    Thanked 326 Times in 320 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

  9. #8
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    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
    Attached Files Attached Files

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,196
    Thanks
    14
    Thanked 326 Times in 320 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 built-in 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
    It will only sum the visible cells, it will not ignore the formulas with SUBTOTALs as the built-in excel SUBTOTAL does. In K3:
    =SumVisible(OFFSET(K5,1,0,5000))

    copy this to L3

    Steve

  11. #10
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    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

  12. #11
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks Steve will try this

    Howard

  13. #12
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    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

  14. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,196
    Thanks
    14
    Thanked 326 Times in 320 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 J-L 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 51-65 in those columns

    Steve

  15. #14
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for your valuable input Steve-this is much appreciated

    I will bring it to my friends attention regarding the formulas in the datatable in rows 51-65 in columns J:L

    Howard

  16. #15
    New Lounger
    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?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •