CODEWITHSUNDEEP
excelcount
timmy_85
timmy_85

Reputation: 13

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

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.

Upvotes: 0

Views: 358

Answers (2)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

You can benefit from SUMPRODUCT:

enter image description here

=SUMPRODUCT((A2:A9&B2:B9<>"")/COUNTIFS(A2:A9;A2:A9&"";B2:B9;B2:B9&"");--(B2:B9<>""))

Upvotes: 0

ru13r
ru13r

Reputation: 194

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)))

Upvotes: 1

Related Questions