How to return and sum up multiple values
 

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.

How to return and sum up multiple values
 

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.

"GEMSBOK1" wrote:

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.

How to return and sum up multiple values
 

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...
--
Regards,
Luc.

"Festina Lente"


"GEMSBOK1" wrote:

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.

How to return and sum up multiple values
 

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



"PapaDos" wrote:

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...
--
Regards,
Luc.

"Festina Lente"


"GEMSBOK1" wrote:

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.

How to return and sum up multiple values
 

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

"Festina Lente"


"GEMSBOK1" wrote:

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



"PapaDos" wrote:

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...
--
Regards,
Luc.

"Festina Lente"


"GEMSBOK1" wrote:

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.

How to return and sum up multiple values
 

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)

"GEMSBOK1" wrote:

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



"PapaDos" wrote:

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...
--
Regards,
Luc.

"Festina Lente"


"GEMSBOK1" wrote:

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.