Regarding Autofilter....

zanoman

Board Regular
Joined
Feb 18, 2004
Messages
63
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
Try using countif() function. this does not distinguish if the cell is hidden or not
Code:
=countif(a:A,"alfred")

where the names are in column A.
 

hh

New Member
Joined
Feb 12, 2004
Messages
22
Zanoman,

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

Rgds,
hh
 

zanoman

Board Regular
Joined
Feb 18, 2004
Messages
63

ADVERTISEMENT

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
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

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

Board Regular
Joined
Feb 18, 2004
Messages
63
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

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top