SubTotal Function

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

when i use the subtotal function i want to use countif
but this is impossible because it isnt one of the 9
listed functions, is there a function that will do the
same job?

Adam
 
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Ref,ROW(Ref)-MIN(ROW(Ref)),,1))*(Ref=Condition
))

The SubTotal bit establishes the visible cells from Ref (that is, the Ref
before any filtering) and the SumProduct bit does a count of Condition
within the filtered Ref. [The SubTotal bit is a construct due to Longre.]
 
Brilliant!

Bob

Aladin Akyurek said:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Ref,ROW(Ref)-MIN(ROW(Ref)),,1))*(Ref=Condition
))

The SubTotal bit establishes the visible cells from Ref (that is, the Ref
before any filtering) and the SumProduct bit does a count of Condition
within the filtered Ref. [The SubTotal bit is a construct due to Longre.]

Adam said:
when i use the subtotal function i want to use countif
but this is impossible because it isnt one of the 9
listed functions, is there a function that will do the
same job?

Adam
Click to expand...
Click to expand...
 
Back
Top