No difference between SUM and SUBTOTAL (9)

[Pradeep Khanna]

I am using SUM function in an Excel Worksheet that carries and autofilter,
But the SUM Fucntion is behaving exactly like the SUBTOTAL Function. In my
Example, I have, say, Sales revenue for a company. I want toplay around and
see on my screen, the results of a filtered list as a Percentage of the
total. However, Since SUM = SUBTOTAL, I keep eeing 100% all the time!!

(For some reason, Pivot cannot be used. Anyways, that is not the point: the
point is that the SUM Fuinction is not behving properly. I have tried working
around it with SUMIF and SUMPRODUCT, but same results)

 

[Ron Rosenfeld]

On Fri, 5 Dec 2008 23:46:00 -0800, Pradeep Khanna <Pradeep

I am using SUM function in an Excel Worksheet that carries and autofilter,
But the SUM Fucntion is behaving exactly like the SUBTOTAL Function. In my
Example, I have, say, Sales revenue for a company. I want toplay around and
see on my screen, the results of a filtered list as a Percentage of the
total. However, Since SUM = SUBTOTAL, I keep eeing 100% all the time!!

(For some reason, Pivot cannot be used. Anyways, that is not the point: the
point is that the SUM Fuinction is not behving properly. I have tried working
around it with SUMIF and SUMPRODUCT, but same results)

If your hidden rows were not hidden by the automatic filter, but rather were
hidden by manually hiding the rows, the SUBTOTAL(9,...) will behave as you
describe.

Try SUBTOTAL(109,...) if your version of Excel supports it. Or, be sure your
rows are hidden by the action of the filter, and not hidden manually.
--ron