I use this formula to get a list of distinct values from column A to column C.
=INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$100),0,0),0))
How can I have a list of distinct values from column A that have a certain text ("a") on column B? Something like:
A B C
1 - a - 1
1 - a - 3
2 - b - 4
3 - a -
4 - a -
5 - b -
A quick and dirty way would be to put in a "helper" column, for example, in D1:
=IF(B1="a",A1,"")
Then just replace A with D in your formula.
Do a pivot table summarizing by Column B first then by Column A (you can do this one down the side and the other across the top) Look in the section where Column B has a value of “A” And you should see counts for the different values of Column A. If you want you can click on the label for Column B that and you get a list of check boxes where you can select on the Value “A” for Column B
You can do this using the SpeedTools LISTDISTINCTS and FILTER.IFS functions
{=LISTDISTINCTS(FILTER.IFS(0,$A$2:$B$100,1,2,"a"),,,,,1)}
You need to select cells in column C, type the formula (without the {}) in the formula bar and then press Control-Shift-Enter to enter it as a multi-cell array formula.
Disclaimer: I develop and sell the SpeedTools product
