This should do it

=SUMPRODUCT(--((A2:A10="Bob")+(B2:B10="Bob")),E2:E10)

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Smonczka" <smonczka@hotmail.com> wrote in message
news:1131036317.783865.272020@g49g2000cwa.googlegroups.com...
> I am trying to calculate commissions for our sales rep. Every month a
> report is run that lists each invoice, the total for that invoice and
> the sales reps associated with the sale. The problem is there may be
> up to four reps per invoice. The report I get looks something like the
> grid below...
>
> # Rep1 Rep2 Rep3 Rep4 $Amount
> 1 Bob Ted Phil 300.00
> 2 Ted Ralph Ted Mike 500.00
> 3 Phil Bob Mike Ted 435.00
> 4 Ralph Bob 198.00
>
> What I need is to come up with a formula that would give me the
> following based off the grid above...
>
> Bob Total $Amount
> Ted Total $Amount
> Phil Total $Amount
> Ralph Total $Amount
> Mike Total $Amount
>
> I was looking at VLookup but could not make it fit the problem. Any
> ideas would be helpful.
>
> As always thanks for any help you can give,
> Steve
>