CODEWITHSUNDEEP
excelexcel-formula
Anthony Roy
Anthony Roy

Reputation: 35

Excel - How to count the number of distinct texts of a specific date inside a table?

I'm trying to count the number of distinct text from a specific date in a data table.

Data Sample with expect result :

Data Sample with expect result

I was able to figure out how to count the distinct element from a range I specify, because I can determine the first and last row containing the date.

=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))

I have tried to modify my formula so that it determines the cell range by itself but without success.

I searched for an answer, using a combination of CELL and MAXIFS, example below, but Excel does not accept the formula. =CELL("row",MAXIFS(A2:A15,A2:a15,D2))

I've looked at the INDEX formula, but I can't figure out how to do what I want to do. 😑

Any idea what I'm doing wrong, or what I should be doing instead?

Thanks, I appreciate it!

Upvotes: 1

Views: 74

Answers (2)

Anthony Roy
Anthony Roy

Reputation: 35

It wasn't easy, but by separating each step of the problem, I was able to solve it.

Note that my solution only works because my dates are sorted.

Here's the final formula in the cell "One formula to rule them all":

=SUMPRODUCT(1/COUNTIF(INDIRECT(CONCATENATE(ADDRESS((MATCH(D3,$A$2:$A$15,0)+1),2),":",ADDRESS(MAX(($A$2:$A$15=D3)*ROW($A$2:$A$15)),2))),INDIRECT(CONCATENATE(ADDRESS((MATCH(D3,$A$2:$A$15,0)+1),2),":",ADDRESS(MAX(($A$2:$A$15=D3)*ROW($A$2:$A$15)),2)))))

Here are my explanations of my process:

Data in Excel

Formula if I select the range : 
=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))

Formula to get the first iteration
=ADDRESS((MATCH(D3,$A$2:$A$15,0)+1),2)

Formula to get the last iteration
{=ADDRESS(MAX(($A$2:$A$15=D3)*ROW($A$2:$A$15)),2)}

Create range from two addresses 
=INDIRECT(CONCATENATE(F3,":",G3))

Formula giving me the expect result
=SUMPRODUCT(1/COUNTIF(INDIRECT(CONCATENATE(F3,":",G3)),INDIRECT(CONCATENATE(F3,":",G3))))

Upvotes: 0

teylyn
teylyn

Reputation: 35935

If you have Office 365 and the new Dynamic Arrays, this sort of formula has become ridiculously easy.

This formula in cell E3:

=COUNTA(UNIQUE(FILTER($B$2:$B$15,$A$2:$A$15=D3)))

Copy down.

You can also generate the unique list of dates with this formula in D3, which spills down automatically and does not need to be copied.

=UNIQUE(A2:A15)

enter image description here

Upvotes: 1

Related Questions