1. ## 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

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

3. 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

4. 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

5. 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

6. 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

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

8. 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

9. 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

10. 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

11. Thanks Steve will try this

Howard

12. 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

13. 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

14. 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

15. Is there a guide online for all the formula we can use for excel?

Page 1 of 2 12 Last

#### Posting Permissions

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