excel - Max value from within ranges -

i've searched question i'm not repeating anything...

i have 3 columns:

column - names (ie david, collin, mary)

column b - sales (50, 60, 45, 88)

column c - week (1, 2, 3, 4, 5)

for example

names sales week  david    1000     1 david    2000     1 david.   500      2 david    1000     2 collin   300       1 collin   500      1 collin   800      2 collin 100       2 

at quick glance can see david's best weekly total 3000 week 1 , collins 900 week 2.

is there formula work out above - 1 gives highest total each person having calculated between weeks?

sorry if haven't been clear :)

try one, works me:

range a1:a8 refers = names

range b1:b8 refers = sale amount

range c1:b8 refers = week number

=max(sumproduct(($a$1:$a$8="david")*($c$1:$c$8=1)*($b$1:$b$8)),sumproduct(($a$1:$a$8="david")*($c$1:$c$8=2)*($b$1:$b$8)),sumproduct(($a$1:$a$8="david")*($c$1:$c$8=3)*($b$1:$b$8)),sumproduct(($a$1:$a$8="david")*($c$1:$c$8=4)*($b$1:$b$8)),sumproduct(($a$1:$a$8="david")*($c$1:$c$8=5)*($b$1:$b$8)))