Good morning,
I am working on this exercise where i have to count how many unique names start with "DOT" or "PRO" in a column. This has to be made respecting filters that may come from other column. I try to list an example of the table in the pic attached.
In this example it is possible to only filter by hair, but i may have more columns.
So, let's say i do not apply any filter:
- PRO = 2;
- DOT = 1.
Filtering for Black hair:
- PRO = 0;
- DOT = 1
I hope I've explained what i need completely, and i thank you in advance for the help!
P.S. An excel formula would be perfect, but VBA code is useful as well.
CodePudding user response：
This what I try base of what I understand to your question, may be it could help
CodePudding user response：
Might be useful to create a column helper and subtotal and for your filter value.
CodePudding user response：
I have implemented this formula at the moment:
=MATR.SOMMA.PRODOTTO(--(SINISTRA(UNICI(A:A);3)="PRO"))
This gives me the correct result when counting Unique PRO's but won't work with the filter. It will always output the same result, no matter which filter is selected. How can i modify this to make it work with the filter?
CodePudding user response：
You can try to use an Array formula:
{=SUM(IF(LEFT(A:A,3)="DOT",1,0))}
To get the brackets around the formula, you need to press ctrl shift enter instead of just enter.
If you want to count the unique ones:
{=COUNTA(UNIQUE(IF(LEFT(A:A,3)="DOT",A:A,"")))-1}
This Array formula creates an array with either the value if it starts with DOT, or an empty string. Then the unique values are counted with UNIQUE and COUNTA. You need to use -1, because all other values are shown as "" and that also counts as a unique value.