Excel formula to count non empty cells in a column for distinct values in an other column

Question

I have a list of members in Excel with their date of deaths (date of death not populated, if member is still alive). For one member there can be more lines because there can be more benefit lines associated. I would like to count the number of distinct members who are dead. An example of my list

As a result, I would like to get 2, the count of distinct dead members in my list.

Answer 1

Excel 2021 supports the dynamic array functions.

You can compose FILTER, UNIQUE and COUNT functions.

IF your IDs are in column A and the dates are in column B.

1. FILTER out items in column A, when the column B is Truthy (non-empty).
2. Apply the UNIQUE function.
3. Apply the COUNT function.

The following formula will produce the count of unique IDs with non-empty dates:

=COUNT(UNIQUE(FILTER(A:A,B:B)))