Count DISTINCT in ColumnA IF ColumnE status='QA'

[jaymz69]

Count DISTINCT in ColumnA IF ColumnE status='QA'

I want to have a new column (RESULTS) where the formula counts only ColumnA (DISTICNLY) IF ColumnE cell = 'QA'



Small example:

Category	Status	RESULTS
blue	QA	3​	<=== ONLY 3 DISTINCT Catgories that have status='QA'
blue	QA
blue	no
gray	no
gray	no
orange	QA
orange	no
pink	QA
pink	no
pink	no
pink	no
pink	QA
purple	no
red	no
red	no
red	no
yellow	no

 

[kweaver]

I thought of 1 way:

=SUM(--ISNUMBER(SEARCH("QA",RIGHT(UNIQUE(A2:A18&B2:B18),2))))

 

[Fluff]

Hi & welcome to MrExcel.
How about

=ROWS(UNIQUE(FILTER(A2:A100,B2:B100="QA")))

 

[jaymz69]

Fluff's worked for my scenario, it is on QUIP and that formula was the only one that worked - though it is at 1 when nothing = 'QA', but that is minor. Thank you all

 

[Fluff]

it is on QUIP

What's that?

though it is at 1 when nothing = 'QA'

The formula I posted will not do that, it will return an error.

 

[jaymz69]

What is that? => Introducing Quip Spreadsheets

 

[jaymz69]

just a collaborative spreadsheet we have to use in company. Not as robust and heavy lifting as Excel. But most formulas can work, from experience I have had so far with it.

 

[jaymz69]

Interesting I tried in another cell but <>'QA' and returns 62 (77 was the QA return) - the total is 98 for the unique categories - I thought I could reverse it and get the unique count <> 'QA' a number somewhere at 20 is what I was expecting (98 total - 72='QA' => 21<>'QA', instead of 66)

 

[Fluff]

In future when posting questions for apps other than Excel, please post them in the General Discussion & Other Applications section of the board & clearly state what you are using.
Thanks

 

[Fluff]

If you have blank rows then you would need to use

=ROWS(UNIQUE(FILTER(A2:A100,(B2:B100<>"QA")*(B2:B100<>""))))