Subtotal function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)
 
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"))
 
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.
 
Back
Top