| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
