Excel problem - Use of a formula with filters

[DrSmyth]

Hi,

I've come up against a bit of an excel problem that is driving me crazy....

I'm using this formula =COUNTIF(Data!F:F,"Yes")/(SUBTOTAL(3,Data!F:F)-1)

Which works fine with my data in it's normal state, however, I also want people to be able to view data based on filter values.

The Subtotal 3 part of the equation works fine with this but the countif function does not adapt to account for the filter...

Can anybody suggest an alternative???

 

[GlennUK]

Use another column with the formula =IF(F2="Yes",1,0) and copy it down, and use SUBTOTAL(9 on it instead of COUNTIF in your formula.




Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.

 

[Eugene99]

Doc,

Try This:

=COUNTIF(Data!F:F,"Yes")/(COUNTA(Data!F:F)-1)

Filters will not change the formula's results.

Eugene

 

[Eugene99]

Doc, I just re-read your post. My formula won't work if you want the results to change with the filters. Glenn's solution will work...

Eugene

 

[DrSmyth]

Cheers Glen... Works like a dream

 

[GlennUK]

Hi Dr Smyth,

glad to have been of some help .



Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.