Named dynamic range, COUNTIF, and advanced filter

[darius]

Hi

This is a bit convoluted. Pls bear with me.

I have a table of survey data, like this

user date question1 question2
user1 1/1/01 excellent good
user2 1/2/02 fair good
user3 5/4/03 poor fair

I have a cell with =counta formula that keeps track of the # of rows in
the table.

I define a dynamic named range for each question column, using an =offset
formula in conjunction with the counta formula above.

I have a separate table that tabulates the results, like this

excellent good fair poor
question1 1 0 1 1
question2 0 2 1 0

All the cells contain countif formulas, e.g.
countif(question1,"excellent") where question1 is named range.

All of this works just fine. The problem is when I define an adv. filter
on the date, the tabulation table is wrong. It still only "sees" the old
table. For example, if I filter out the date to after 2001, it seems to
know that they are only 2 rows in the table now, but it still just pick
the top 2 rows in the unfiltered table, instead of the bottom 2 rows that
are now visible.

filtered data

user date question1 question2
user2 1/2/02 fair good
user3 5/4/03 poor fair

tabulation table

excellent good fair poor
question1 1 0 1 0
question2 0 2 0 0

and if the filtered data has only 1 row, it'll just see the 1st row in
the unfiltered table. and so on.

What am I doing wrong?

thanks

 

[Don Guillett]

try using SUBTOTAL function.

 

[darius]

try using SUBTOTAL function.

Unfortunately, subtotal as far as I can tell doesn't have a countif like
function. Am I wrong?

 

[Don Guillett]

sure it does
SUBTOTAL
See Also

Returns a subtotal in a list or database. It is generally easier to create a
list with subtotals using the Subtotals command (Data menu). Once the
subtotal list is created, you can modify it by editing the SUBTOTAL
function.

Syntax

SUBTOTAL(function_num,ref1,ref2,...)

Function_num is the number 1 to 11 that specifies which function to use in
calculating subtotals within a list.

Function_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Ref1, ref2, are 1 to 29 ranges or references for which you want the
subtotal.
Remarks

a.. If there are other subtotals within ref1, ref2,. (or nested
subtotals), these nested subtotals are ignored to avoid double counting.
b.. SUBTOTAL will ignore any hidden rows that result from a list being
filtered. This is important when you want to subtotal only the visible data
that results from a list that you have filtered.
c.. If any of the references are 3-D references, SUBTOTAL returns the
#VALUE! error value.

 

[darius]

sure it does
SUBTOTAL
See Also

Returns a subtotal in a list or database. It is generally easier to
create a list with subtotals using the Subtotals command (Data
menu). Once the subtotal list is created, you can modify it by
editing the SUBTOTAL function.

Syntax

SUBTOTAL(function_num,ref1,ref2,...)

Function_num is the number 1 to 11 that specifies which function
to use in calculating subtotals within a list.

Function_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

[snip]

Sorry for being dense, but how do I use COUNTIF (not COUNT or COUNTA)
with the SUBTOTAL function? Can I write a custom function? How?

 

[Debra Dalgleish]

Laurent Longre created a formula that lets you work with visible rows
after a filter. For information see, Power Formula Technique in this
article at John Walkenbach's web site:
http://j-walk.com/ss/excel/eee/eee001.txt

For example, to count "Poor" cells in column C, after a filter on
another column, you could use the following, where there are no blank
cells in column A, and cell H1 contains the word "Poor":

=SUMPRODUCT(($C$2:$C$200=H$1)*(SUBTOTAL(3,OFFSET($A$2:$A$200,ROW($A$2:$A$200)-MIN(ROW($A$2:$A$200)),,1))))

sure it does
SUBTOTAL
See Also

Returns a subtotal in a list or database. It is generally easier to
create a list with subtotals using the Subtotals command (Data
menu). Once the subtotal list is created, you can modify it by
editing the SUBTOTAL function.

Syntax

SUBTOTAL(function_num,ref1,ref2,...)

Function_num is the number 1 to 11 that specifies which function
to use in calculating subtotals within a list.

Function_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

[snip]

Sorry for being dense, but how do I use COUNTIF (not COUNT or COUNTA)
with the SUBTOTAL function? Can I write a custom function? How?