I want to be able to apply the filters to my count()

[erison]

Hi all. I have just gotten started on doing these things in Excel, so I hope you will have patient will low level questions!

I am trying to build a CRM-document for sales. I have an order list which contains fields like customer number, customer name, category, status, contact name, and so on.
Now I’m building a statistics tab, where I for example want an answer to the question “How many customers with the status “OK” do I have?”. So long fine, using

Please Login or Register  to view this content.

Here comes my challenge. The customer list has a nice filtering, so I can filter on for example customers in Category 3. (Normal excel filtering) This I want to be able to use in my statistics as well. I want to be able to answer the question “How many customers in category 3 with the status “OK” do I have?”

Basically, I would like to see how many customers I have within every status (let’s call them Status1, Status2, Status3), when I do certain filters in the customer tab. This should look somewhat like this:

Status Count
Status1 24
Status2 58
Status3 29

(And above values should change when I change filtering on specific parameters from the customer list.)

Maybe a little bit messy but I hope you understand

Thank you in advance!

[vidyuthrajesh]

Hi Erison,

Not sure on the need of "filter" part and not sure if this function can be of any help. you may give a try

try using countifs function..for example..if your category is in column B

Please Login or Register  to view this content.

[erison]

Thank you! This does actually work But if I would like it more graphical, like an actual scroll down, with possibility of filtering one or more columns. (Just like the data -> filter -function works...) Is there a way of doing it that way?

[FDibbins]

not sure if i understand your requirement, but perhaps you could put the "OK" and "Status1" on a dropdown, and then let vidy's formula reference them, instead of hard-coding them?

[oeldere]

You can work with the formula subtotal.

You can work with a pivot table.

You get better help if you post an Excel-example of your workbook, without confidential information.

Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.

Use BEFORE/AFTER sheets if that helps make it clearer.

[erison]

FDibbins, thanks the dropdown would work when I have static number of options, but I'm also looking for some more "automated" way. I also want to be able to choose more than one criteria (such as the filter function).

Examplefile attached, does that make a more clear picture?

examplefile.xlsx

Kind Regards,
Erik

[FDibbins]

this may not be exactly what you want, but the table i created gives you the flexibilty to change your criteria as you need.

I only set it up for the 1st 2 columns, but if you want to adjust the ranges for other columns, its easy enough to do. you could even make up multiple tables if you want