counting unique or equal text

[jacob farino]

First of all, thanks to everyone who answers our questions--it is a HUGE
help.

Here is my current dilema:
I have a totals bar on Row 1 for a sales spreadsheet. It tallies the
total amount of "sales" made (COUNTA...). What I need is how many sales per
salesperson there are.

For instance, I have a total of 20 loans, counted and displayed in B1 (all
my totals/data are displayed in row 1, the acutal data contained below).
Column A is for the name of the salesperson, so it might look like:

A B C D
John.....Customer name.....sale amount....etc....
Bill....et al.,
John....et al,
Lucy....et al,

What I want is 1.333, which is the number of total sales per unique
salesperson's name, to be calculated.
I hope this makes sense. (currently what i have is =B1/20 where B1 is the
number of sales counted, divided by my current number of salespeople, but as
I add or delete salespeople, it would be nice to have it calculated
automatically.)

Jacob

 

[Ragdyer]

I'm assuming that by sales, what you really mean is transactions.

So, if the number of names entered in column A is equal to the number of
transactions, then we count the total names and divide by the unique names,
and I guess we can round off to two decimal places:

=ROUND(COUNTA(A2:A100)/SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))
,2)