This is a repeat of the first part of your previous post ... to create a
unique list first ... so that the archives are complete.
<<<"To create the unique list:
Say your sales person list was in A1 to A2000, and the sales amounts were in
B1 to B2000
In C1 enter"
=A1
In C2, enter this *array* formula:
=IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$2000&""),0)),"",INDEX(IF(ISBLANK($A$1:$A$2000),"",$A$1:$A$2000),MATCH(0,COUNTIF(C$1:C1,$A$1:$A$2000&""),0)))
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.
*AFTER* the CSE entry, drag down to copy as far as there are returns (no
blanks cells).">>>
Now, in D1, to total the sales of each person in the unique list, enter this
formula:
=IF(C1<>"",SUMIF(A$1:A$2000,C1,B$1:B$2000),"")
And drag down to copy as needed.
Same caveat as before:
One problem that you might run into is that similar appearing names are *not
exactly identical*, and therefore will count as 2 persons.
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
I want to summarise number of sales by salesperson. The raw data is in two
columns eg
Pat 12
Sarah 4
John 2
Sarah 6
Pat 1
John 5
The answer table I'd be looking for is
Pat 13
Sarah 10
John 7
.....if my maths is right <G>
Can SKS help
Tx
Rob
