How to return and sum up multiple values

[Guest]

Hi I have a problem which a straight forward SUMIF and VLOOKUP don't seem to
cope with, although it's probably that I don't know how to use them

I have 3 sheets Sheet1 = Summary, Sheet2 = list of groups with valid names,
Sheet3 = raw data (imported)

Sheet1 .... looks like this
A B C D
1 Group Name Units Sales C.O.S.
2
3 Group1
4 Group2
5 Group3


Sheet2 .....looks like this
A B C
D
1 Group1 Group2 Group3
Group4
2 Able Jake Kate
Lidia
3 Sue Francois Dermot
Sam
4 Terry Jo
Richard
5 Tim
Annelise
6
Jonathan


Sheet3 ...... looks like this

A B C D
.........ETC
Names Units Sales C.O.S.
Lidia 1 5666.55 3456.21
Sue 6 1238.88 6543.21
Francois 4 12456.76 7891.31
Dermot 2 11987.32 4569.99
Richard 2 9876.54 2999.87

Tim 1 -45.23 1500.00
Annelise 1 8257.16 1854.21



What I'm trying to do is write a formula in Sheet1 B3 which
will lookup Sheet2 A2:A6 and then go to Sheet3 and wherever it finds
a name in Sheet3 ColumnA matching one from Sheet2 A2:A6 return the value and
sum them up. This is to be done for all the groups in Sheet1.

I apologies if my terminology is wrong, but I'm rather a novice at Excel and
the person who would usually write these spreadsheets is off ill for the next
few weeks.

 

[Guest]

I'm afraid the sheets haven't held their layout as typed in. I think Sheets 1
& 3 should still be 'decipherable', but Sheet2 has the groups in row1 columns
A, B, C, D and the names are in columns below them.

Hope this helps.

 

[Guest]

Is the list of groups on sheet1 alway complete and in the same order than
sheet2 or could some groups be missing ?

If so, if you can transpose the table on sheet2, things would be a lot
simpler, faster and easier...

 

[Guest]

Sheet1 will always have all available groups, but they may not all be
populated as not all groups members may make sales in a period (Sheet3 - raw
data).

By transposing the table in Sheet2, I assume you mean orientating the groups
so that instead of being in each column, they are all in column A, one under
another?

Regards

 

[Guest]

What I mean is to put the group members in the same row instead of the same
column...

 

[Guest]

Try this formula in B3 copied across and down as necessary

=SUMPRODUCT(--ISNUMBER(MATCH(Sheet3!$A$2:$A$9,INDEX(Sheet2!$A$2:$D$6,0,MATCH($A3,Sheet2!$A$1:$D$1,0)),0)),Sheet3!B$2:B$9)