Counting Uniques

[psycoperl]

Hi, I have a worksheet in Excel that is a log of transactions (after we run a process, we paste the log information at the bottom to maintain all history) What I would like to be able to find out and include in a separate worksheet in the same workbook file, is how many Unique end users did we service? A simple count would not help as we could have multiple transactions for the same person.

Is there a Count Unique function or some other method to achieve this?

 

[sandy666]

maybe PivotTable with DataModel then CountDistinct

 

[Fluff]

Or something like
=SUMPRODUCT(1/COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100))

 

[Aladin Akyurek]

By means of a formula... Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(UsersRange<>"",MATCH(UsersRange,UsersRange,0)),ROW(UsersRange(-ROW(INDEX(UsersRange,1,1))+1),1))

 

[Aladin Akyurek]

@ Fluff

Not efficient and the way you wrote it will fail because of empy/blank cells.

 

[psycoperl]

By means of a formula... Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(UsersRange<>"",MATCH(UsersRange,UsersRange,0)),ROW(UsersRange(-ROW(INDEX(UsersRange,1,1))+1),1))

@Aladin - what does the Control Shift Enter do?

 

[Aladin Akyurek]

@Aladin - what does the Control Shift Enter do?

One type of formulas, the so called array-processing formulas, must be signaled explicitly as Excel currently requires. That signaling is done with control+shift+enter, that is, by pressing down the control and the shift keys at the same time while hitting the enter key. When done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[Fluff]

@ Fluff

Not efficient and the way you wrote it will fail because of empy/blank cells.

Thanks for that, unfortunately when I try yours I get "You've entered too many arguments" & it highlights the final 1

 

[Aladin Akyurek]

Thanks for that, unfortunately when I try yours I get "You've entered too many arguments" & it highlights the final 1

It's:

{=SUM(IF(FREQUENCY(IF(UsersRange<>"",MATCH(UsersRange,UsersRange,0)),ROW(UsersRange)-ROW(INDEX(UsersRange,1,1))+1),1))}

 

[Fluff]

That does it & way quicker than my attempt.
Cheers