Regarding Autofilter....

zanoman

Board Regular
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

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

SIXTH SENSE

Well-known Member
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
Zanoman,

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

Rgds,
hh

zanoman

Board Regular
Thnx alot both of you guys!

Eric

zanoman

Board Regular

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

Well-known Member
Hi!
are values in P4 tp P139 Numeric?

GorD

Well-known Member

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
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
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,088
Messages
5,857,290
Members
431,869
Latest member
Avinashz

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.

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

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