Using the SUBTOTAL Formula in conjuction with Sorting or can you use SUBTOTAL to count strings of text?

Question

Hello,

I am using the SUBTOTAL formula to only count unhidden cells when using filters, but when I use sort, the cell names in the formula do not move around with the sorting at my results become wrong

Here is an example:

Here is my dataset not showing formulas:

Here is the formula view:

Lets say I filter the software column to just show Excel, here are my results:

These are the results that I want. (I don't want to count the hidden cells)

But then lets say I sort the name column so the names now appear in alphabetical order.

This will change the order of my rows and usually formulas will change as well, but the cell names I put into the SUBTOTAL formula do not change I my Software totals become wrong when I use filter (see last screenshot):

As you can see, the totals now show 1 Excel and 1 Word which we know is wrong.

Is there anyway the SUBTOTAL formula can be used with a string of text so "Excel" will always be counted instead of the actual cell.

Or are there so other formulas I should be using to do this.

Any help will be greatly appreciated.

(Sorry for all the pics, but I felt like an example would be the best way to explain my problem)

Thanks,

-Dan

Answer 1

Hi,

You may simply create a Pivot Table- drag the Software column to the row labels and to the Value area section.

Hope this helps.

Answer 2

Hello Ashish,

Thanks for the reply,

I have been busy at work and finally got around to trying the Pivot Table Method, but it does not work.

So I created the Pivot Table as you instructed (shown below):

But When I use the filters, the "Count of Software" Column does not update. see below:

Ex: lets say I just want to see Bob's software count, so I fiter the "User" Column to "Bob":

As you can see the Pivot Table shows all the software and not just Bob's

I need the counting to work with Filtering (by not counting hidden rows) AND Sorting (so when I sort it doesnt mess up my formulas, as I explained in my first post)

Any help would be greatly apprieciated as this would make my record keeping and report making easier

Answer 3

Hi,

Drag the User column to the Report filter area of the Pivot Table and in the Report filter drop down (not base data), select Bob.

Hope this helps.