Subtotal function

[Underdog]

I am having difficulties with the subtotal function. How do you subtotal
(using the function) a specific word from a list? I have a list with
"apples" and "oranges" down column N. I want to only subtotal oranges.
Right now when i subtotal a full range (using "3" COUNTA) it gives me a count
of all cells.

Here's the thing, the list i am using cannot be sorted. The list cannot be
manipulated at all except through filtering. I am using the subtotal
function so i can subtotal only that data that has been filtered.

Any help on this would be wonderful.

 

[Bob Phillips]

Subtotal is of no use if the list is not sorted.

Try adding formula like

=SUMIF($A$1:$A$100,"apples")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(Sheet1!
$A$1:$A$100,1,1),ROW(Sheet1!$A$1:$A$100)-ROW(INDEX(Sheet1!
$A$1:$A$100,1,1)),0))=1),--(Sheet1!$N$1:$N$100="oranges"))

 

[Frank Kabel]

Hi
just as addition:
- the parameter 101-109 are only available in Excel 2003
- the parameters 1-9 WILL exclude filtered cells/rows. They would
process MANUALLY hidden cells/rows. So if you only apply filter use
'1-9' as parameter.