CODEWITHSUNDEEP
excelexcel-formula
Elvin Ibishli
Elvin Ibishli

Reputation: 33

Count without duplicates and with criteria in a different row

I have 2 rows, one has categories, and in the other I have some values indicating the category selection, which can be different types of indications- x, y or blank.

Category 1   Category 1 Category 1 Category 2 Category 2 Category 3 Category 3
    x            y          x                               x

Using these 2 rows, I would like to count the number of unique categories which has any kind of value indication below. Values don’t really matter whether it’s x or y - just that I have different kinds of values for consideration. So in the case of the above, I should get a result of 2, because I have category 1 and category 3 with some values underneath and I don't count them multiple times.

Upvotes: 0

Views: 260

Answers (1)

Ike
Ike

Reputation: 13014

You can use this formula - if you have Excel 365:

=COUNTA(UNIQUE(FILTER(A1:G1,A2:G2<>""),TRUE))

if your data are in A1:G2.

It first filters the header row based on non-empty cells in row 2. Then counts the unique values.

Upvotes: 2

Related Questions