Using AutoFilter In Excel, I can't get Excel to recalculate only .

[Guest]

Hi,

I have created a spreadsheet with numbered values in cells. At the top of
the spreadsheet I have an area for calculations. Within the spreadsheet I
have several repeated lines of text with numbers tied to them. I thought
that when I filtered the spreadsheet for a certain lineof text or name, the
appropriate calculations would be performed only on/for that line of text or
name. This is not happening as Excel is making calculations from the entire
sheet. Is it possible to have Excel filter for a certain line of text, there
may be 200 entries or so and calculate only what is being filtered. I can
group these to avoid the problem, but then the spreadsheet is added to daily
with numerous other lines of text or names and that would not be feasable. I
need to filter for a certain line of text or name and have Excel calculate
only those values tied to it and not the entire sheet. Is this possible?

Thanks for any help in advance!!

 

[Jack Sheet]

Not sure if I completely understand the question, but have you tried using
=SUBTOTAL() instead of =SUM() over the filtered range?

 

[Jason Morin]

You can perform basic sum and count calculations on
filtered data using the SUBTOTAL worksheet function. Look
under XL Help for instructions on how to use it.

HTH
Jason
Atlanta, GA

 

[Guest]

Use the SUBTOTAL function, see help, for example if you use

=SUBTOTAL(9,B2:B500)

then filter on text in A2:A500 and

the aabove function will only sum visible cells as opposed to SUM(B2:B500)
which will always sum all rows


Regards,

Peo Sjoblom