0

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?

danjswade
  • 557
  • 2
  • 8
  • 16
  • Possible duplicate of [Count unique values in a column in Excel](https://stackoverflow.com/questions/18586815/count-unique-values-in-a-column-in-excel) – Michael Z. Jul 05 '17 at 22:47
  • No, as I'm looking at two different columns, rather than just duplicates in a single column – danjswade Jul 06 '17 at 09:57

1 Answers1

1

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

  • Perfect! Doesn't work if there are any blank rows, so need to delete those before it works. – danjswade Jul 05 '17 at 15:47
  • Hey Jeeped, this is a duplicate. Maybe do your research first. https://stackoverflow.com/questions/18586815/count-unique-values-in-a-column-in-excel – Michael Z. Jul 05 '17 at 22:45
  • @Jeeped, way to rack up those reps on answering duplicates that are very easy to find. This is exactly what you just came down on me for. – Michael Z. Jul 05 '17 at 22:52