Subtotal and Countif

[Don Niall]

Hi,

I have a s/s with multiple columns of data.
In columna A I have a subtotal(3,DATARANGE) formula, and
in column B a countif(DATARANGE,"Y").
Is there any variation on the countif formula that will
work in synch with the subtotal formula?
For example if I filter a specific piece of data the
subtotal will return the revised count. The countif
formula howevere will continue to report on the entire
datarange - not the subtotal?

Any ideas?

Don-

 

[Frank Kabel]

Hi
use:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(DATARANGE,1,1),ROW
(DATARANGE)-ROW(INDEX(DATARANGE,1,1)),0))=1),--
(DATARANGE="Y"))