Count without duplicates and with criteria in a different row-CodePudding

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.

CodePudding user response：

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.