How do i analyse visible rows only in excel?

[Dr Happy]

I have created a worksheet consisting of columns consisting of name, work
group ( a 2 digit alpha numeric), gender and then some numerical data. I want
to autofilter on work group and then perform analysis on only the visible
rows left. Obviously I only want 1 summary row at the bottom which will
change as I apply different criteria to the auto filter (by selecting
different work groups). I can get count functions to work with sub-total but
I need to use the countif function. Any clues?

 

[Khaldoun]

Hi,

the first solution jumping to my mind would be one of the two: -
1- either you use advanced filter and copy data to a new sheet where you can
analyse it

or

2- better use Pivot Tables.

Will any of these do?

Khaldoun
Specialized Technologies
Saudi Arabia

 

[Bob Phillips]

Here is an example where B2:b20 is filtered, and a condition in A2:A20 is
tested

=SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1)))*(C2:C20=
"a"))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bernie Deitrick]

You are either going not far enough, or too far. If you want to ue Filters and SUBTOTAL, then you
need to completely filter your data to get your count, along with your additional criteria from
COUNTIF - that is the equivalent of using COUNTIF. So apply another level or two of filtering.

Or don't use filters at all, and use pivot tables, hiding what you don't need.

Or - use horribly complicated SUMPRODUCT Formulas.

HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

Hi Roger,

Problem is that it does handle filtered rows, but not rows hidden by some
other method. I posted a solution that incorporated a UDF for that case a
few days ago, which also handles the filtered data. Might just use that as
my standard.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Hi Roger,

My Excel of choice is still 2000 (£ being my driver :-( ), and that only
goes up to 11, so I didn't know that. Thanks, I have learned something.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi Bob

That's cool.
Never thought about using the Subtotal function inside Sumproduct like that.

Regards

Roger Govier

 

[Roger Govier]

Hi Bob

It will handle rows hidden, as opposed to filtered, if you substitute 103 for 3
=SUMPRODUCT((SUBTOTAL(103,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1)))*(C2:C20="a"))

This is a feature that is present in Xl2003, and maybe in XL2002(??)

Regards

Roger Govier

 

[Roger Govier]

Hi Bob

(£ being my driver :-( )

what other driver is there???
Waiting for Excel 12 perhaps!!

I just checked out XL2002 and it does not exist in that version.

Regards

Roger Govier