How do i analyse visible rows only in excel?

D

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?
 
K

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
 
B

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)
 
B

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
 
B

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)
 
B

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)
 
R

Roger Govier

Hi Bob

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

Regards

Roger Govier
 
R

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top