Excel Gurus - I need some help!

[Taren Nighteyes]

Problem: I am using a large number of filters in an excel file. I have a Grand Total at the bottom, which totals ALL of the rows. I want this Grand Total to mirror the data visible (filtered) on the spreadsheet. So, if I filter so I just have X displayed, I want the grand total to equal X. The current formulat displays X, Y, and Z regardless of the filter.

Any solutions/formulas to solve this problem?

Thanks for any help!

Taren Nighteyes

 

[Drawmack]

what are you using currently? look into sumif

 

[BlackBart]

Problem: I am using a large number of filters in an excel file. I have a Grand Total at the bottom, which totals ALL of the rows. I want this Grand Total to mirror the data visible (filtered) on the spreadsheet. So, if I filter so I just have X displayed, I want the grand total to equal X. The current formulat displays X, Y, and Z regardless of the filter.

Any solutions/formulas to solve this problem?

Thanks for any help!

Taren Nighteyes

I'm not sure it needs to be this complicated, but if you create a function in Visual Basic:
Public Function RowVis(CellName As Range) As Boolean
RowVis = Not (CellName.EntireRow.Hidden)
End Function

You can use this function to have a cell track whether or not the row it is in is visible. You can then use that result in conjunction with a SUMIF to sum up only visible rows.

 

[Heretic Apostate]

Wouldn't the SUBTOTAL function work?

Okay, just checked MS Excel 2000's help feature. According to that,

SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered.

So, just do SUBTOTAL(9,cell range). (The 9 means that the subtotal will use Sums. Subtotals can be done using 11 different functions, #9 being sums.

Hope this helps. It doesn't need any complicated formulas or such.

(Thinking to myself, "Wow, I could actually answer a question on these boards, instead of just asking 'em!" )