How to find this total?

[TimN]

I have a spreadsheet set up as the following:

In Column A1 I have salesman names.
In Col B1 I have each individual unit sale with a sum at the bottom.

Example:
(A1) (B1) (C1) (D1)
Tim 45 Tim 65
10 Mary 7
8
2
Total 65

Mary 4
3
Total 7

How can I do a lookup that in say cell C1 gives me salesman name and in cell
D1 gives me the total sales for that salesman? Each day new individual sales
are added and perhaps a new salesman (salesperson)is added also.

Thanks!

 

[Bob Phillips]

Cumbersome, but it works

=SUM(INDEX($B$1:$B$100,MATCH(C1,A:A,0)):INDEX($B$1:$B$100,
IF(MIN(IF(INDEX($A$1:$A$100,MATCH(C1,A:A,0)+1):A100<>"",ROW(INDIRECT(MATCH(C1,A:A,0)+1&":100"))))=0,100,MIN(IF(INDEX($A$1:$A$100,MATCH(C1,A:A,0)+1):A100<>"",ROW(INDIRECT(MATCH(C1,A:A,0)+1&":100"))))-1)))

as an array formula, commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)