Hi Team,

was wondering if you could help with this one.

Summary Sheet :

A B
1 Team # referral calls
2 Red Team ??
3 Blue Team ??
4 Green Team ??

7 All team members # referral calls
8 Adam =COUNTIF(Data!$G:$G,B14)
9 Bill =COUNTIF(Data!$G:$G,B14)
10 Alison =COUNTIF(Data!$G:$G,B14)
11 Allana =COUNTIF(Data!$G:$G,B14)


Lookup Sheet
A B
1 Name Team
2 Adam Red
3 Bill Red
4 Alison Blue
5 Allana Green


I am trying to have a "Team Totals" table at the top of the individual
totals, withour having to reference the team next to the individual on that
page. Instead I have a lookup sheet that allocates the team to the people....

in "Excel talk" in cells B2:B4

=sum(B7:B11) if B4 = VLOOKUP(B14,'Lookup sheet'!$A$2:$B$5,2,FALSE)

of course that will not work for a few reasons, one being the lookup in not
set up for an array >.<

any ideas?

> =sum(B7:B11)

Don't you mean: =sum(B8:B11)

With:

A2 = Red
A3 = Blue
A4 = Green

Try this in B2 and copy down:

=SUMPRODUCT(--('Lookup Sheet'!B$2:B$5=A2),--(ISNUMBER(MATCH(A$8:A$11,'Lookup
Sheet'!$A$2:$A$5,0))),B$8:B$11)


--
Biff
Microsoft Excel MVP


"Madduck" <(E-Mail Removed)> wrote in message
news:38330AA9-FB0D-42EA-B6C2-(E-Mail Removed)...
> Hi Team,
>
> was wondering if you could help with this one.
>
> Summary Sheet :
>
> A B
> 1 Team # referral calls
> 2 Red Team ??
> 3 Blue Team ??
> 4 Green Team ??
>
> 7 All team members # referral calls
> 8 Adam =COUNTIF(Data!$G:$G,B14)
> 9 Bill =COUNTIF(Data!$G:$G,B14)
> 10 Alison =COUNTIF(Data!$G:$G,B14)
> 11 Allana =COUNTIF(Data!$G:$G,B14)
>
>
> Lookup Sheet
> A B
> 1 Name Team
> 2 Adam Red
> 3 Bill Red
> 4 Alison Blue
> 5 Allana Green
>
>
> I am trying to have a "Team Totals" table at the top of the individual
> totals, withour having to reference the team next to the individual on
> that
> page. Instead I have a lookup sheet that allocates the team to the
> people....
>
> in "Excel talk" in cells B2:B4
>
> =sum(B7:B11) if B4 = VLOOKUP(B14,'Lookup sheet'!$A$2:$B$5,2,FALSE)
>
> of course that will not work for a few reasons, one being the lookup in
> not
> set up for an array >.<
>
> any ideas?

Thanks Biff. Worked a treat... it was the Isnumber again that eluded me...

very grateful to have such a pool of helpful people to help us all out..

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Madduck" <(E-Mail Removed)> wrote in message
news:1A6C6921-F5F0-4117-B038-(E-Mail Removed)...
> Thanks Biff. Worked a treat... it was the Isnumber again that eluded me...
>
> very grateful to have such a pool of helpful people to help us all out..