From: Jenny S on
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

From: Jacob Skaria on
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
>