using Sumproduct with named ranges

Hi,

Hope someone can help.
I have named ranges in tab 1, Column E=B is for Branch, Column F=G is for
Game, & Column G=P is for points. Named ranges are all from row 6 - row 501.
Data is input into tab 1, summary in tab 2.

In Tab 2 I have the branches from A3:A9, Games from C2:L2

In C3 I am wanting to add all the points for the branch A3 for the game in
C2 that appear in the named range P. I have tried the equation below with
"P" which does not work, and also tried G6:G501.

=SUMPRODUCT((B=$A3)*(G=C$2)*(G6:G501)) gives me no values, won't add up the
points in Column G (which is named rang P)

Hope this makes sense - help would be great as I am scoring for an event
with this on Sunday.

Many thanks, in advance.
Jenny

Try
=SUMPRODUCT((b=$A3)*(g=C$2),p)

--
Jacob (MVP - Excel)


"Jenny S" wrote:

Hi,

Hope someone can help.
I have named ranges in tab 1, Column E=B is for Branch, Column F=G is for
Game, & Column G=P is for points. Named ranges are all from row 6 - row 501.
Data is input into tab 1, summary in tab 2.

In Tab 2 I have the branches from A3:A9, Games from C2:L2

In C3 I am wanting to add all the points for the branch A3 for the game in
C2 that appear in the named range P. I have tried the equation below with
"P" which does not work, and also tried G6:G501.

=SUMPRODUCT((B=$A3)*(G=C$2)*(G6:G501)) gives me no values, won't add up the
points in Column G (which is named rang P)

Hope this makes sense - help would be great as I am scoring for an event
with this on Sunday.

Many thanks, in advance.
Jenny