Count if and Vlookup using a lookupsheet

[Guest]

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?

 

[T. Valko]

=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)

 

[Guest]

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..

 

[T. Valko]

You're welcome. Thanks for the feedback!