Regarding Autofilter....

[zanoman]

Is there a way to have a cell count how many items i have in a collumn when im using a certain sort order from the Autofilter function?

Example;

I have 100 cells in a collumn containing text. 20 of the cells are the same, lets say "Alfred" Is it possible to have a cell counting how many "Alfreds" i have when i use the Autofilter and pushing Alfred?


And how come i cant have a blank row in between two rows where i want to use Autofilter?


Eric

 

[SIXTH SENSE]

hi!
Try using countif() function. this does not distinguish if the cell is hidden or not

=countif(a:A,"alfred")

where the names are in column A.

 

[hh]

Zanoman,

look at the subtotal function... It does the same as sum, etc but only for displayed lines.

Rgds,
hh

 

[zanoman]

Thnx alot both of you guys!


Eric

 

[zanoman]

Hmm....

I typed in the example showing in the help file;

=SUBTOTAL(9,P4:P139)

But then i got an error; This formula contains an error. But if use this forumla;

=SUBTOTAL(9;P4:P139)

it says "0" always, no matter what my Autofilter criteria are set to. Even though it displays 10 entries, it says "0".. How come?

Have in mind that its "text", not numbers in the cells.

Eric

 

[SIXTH SENSE]

Hi!
are values in P4 tp P139 Numeric?

 

[GorD]

The code 9 at the start of your function gives you a sum not a count. Change the 9 to a 2, which specifies count. This still won't work for text items though. How about adding a number to each row 1,2,3, etc and refering to that as your count criteria.

 

[zanoman]

I found a solution... Beacause i have both numbers and text it only counts the numeric cells. But thats not a problem, because when you set the Autofilter on, you can only filter on the rows at a time. So i can set the;

=SUBTOTAL(2;R4:R139) in a row where i have numeric cells, and then just link to this cell from another cell!


Eric


OH; Can i somehow get the formula; =SUBTOTAL(2;R4:R139)?? to count numeric entrie with ?? inside? For example; I have some cells like this;

1
2
2
2??
2??

Can i get the formula to include the cells with the ?? behind??

 

[GorD]

Not sure why you use a ; syntax should be a comma but not sure if it makes any diff.

If you have a list of numeric values you can filter yout list using "custom" and set two criteria, one of which would be "contains" ??

As long as the subtotal formula refers to the numeric column, it will work.