Counting fileterd cells with spesific value

[Sjur]

I have å spreadsheet with the following count function:
=COUNTIF(V7:V197;"Closed")
=COUNTIF(V7:V196;"Open")
This gives me the sum of cells containing Open/Closed.

After applying a filter in B:7 I still get the same number of open/closed.
Is it possible to use countif, or similar, to count numers of open/closed
records in V:7-197 after filtering ?

 

[Mike H]

Try

=SUMPRODUCT(SUBTOTAL(3,OFFSET(V7:V197,ROW(V7:V197)-ROW(V7),0,1)),--(V7:V197="Closed"))

Mike

 

[Sjur]

I'm using 2003 and when I try this formula, I get frmula failure on offset
and row(v/),0,1

But thanks for trying

 

[Mike H]

Hi,

I can't replicate that error. Did you copy and paste the formula or did you
re-type it. Copy and paste is the best option.

Mike

 

[ShaneDevenshire]

Hi,

If your having a problem entering the formula here is a simplier, but less
sophistocated approach:

Create a dummy colum say W. and enter the formula
=IF(SUBTOTAL(3,V7),V7,"")
Copy it down column W.
In two blank cells enter the formulas:
=COUNTIF(W7:W197,"closed")
=COUNTIF(W7:W197,"open")