How to find this total?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TimN - 07 Dec 2006 23:25 GMT

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!

Reply to this Message

Bob Phillips - 07 Dec 2006 23:57 GMT

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

>I have a spreadsheet set up as the following:
>
[quoted text clipped - 20 lines]
>
> Thanks!

Reply to this Message