CODEWITHSUNDEEP
excelexcel-formula
danjswade
danjswade

Reputation: 567

Count Entries in Column Ignoring Duplicates

I have a table which handles timesheets. One column is for weekending dates, next column is name and third is hours worked.

W/E         Name        Hours

25/05/2017  J. Smith    37.5
18/05/2017  J. Smith    37.5
25/07/2017  J. Smith    37.5
25/07/2017  D. Bloggs   20
25/07/2017  D. Bloggs   10
25/07/2017  D. Bloggs   7.5

I need to find how many unique timesheets I have. So based on the above, I have 3 timesheets from J Smith but only 1 timesheet from D Bloggs, albeit split into 3. So my total unique timesheets is 4.

Can anyone recommend a good way, formula or function that would get this done without deleting or editing my data manually?

Upvotes: 0

Views: 65

Answers (1)

user4039065
user4039065

Reputation:

Try,

=SUMPRODUCT(1/COUNTIFS(A2:A7, A2:A7, B2:B7, B2:B7))

enter image description here

If your data contains blank rows, change the numerator of the division to not accept blanks and add blanks to the denominator; the latter to avoid a #DIV/0! error.

=SUMPRODUCT((A2:A8<>"")/(COUNTIFS(A2:A8, A2:A8, B2:B8, B2:B8)+(A2:A8="")))

If there is a blank then the numerator will be zero and the denominator will not. Any fraction with zero as the numerator is zero so it doesn't matter what the denominator is as long as it is non-zero.

enter image description here

Upvotes: 1

Related Questions