| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding fileds in a list based on a lookup
I have a sheet containing 3 columns. The first column is a name, the second
and third are numbers. I would like to have a foumula that will look down the list for each occurance of a specific name, and upon finding it, add the contents of column 2 and 3 on that row to a running total for all of the occurances found. Ex:ample: | 1 | 2 | 3 ---------------------------------- A | Gregory Day | 2 | 2 B | Gregory Day | 2 | 2 C | Gregory Day | 2 | 2 Should return an answer of 12. Does that make sense? The only additional question is that the formula would be a sheet 1, the data on sheet 2. I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations there of, with out luck. Any help would be greatly appreciated. Thank you, |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding fileds in a list based on a lookup
Try this version of SUMPRODUCT:
=SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5)) Assuming the data is in Sheet2 A1:C5 and the formula goes into Sheet2 per your request. H.T.H. -- when u change the way u look @ things, the things u look at change. "Gregory Day" wrote: I have a sheet containing 3 columns. The first column is a name, the second and third are numbers. I would like to have a foumula that will look down the list for each occurance of a specific name, and upon finding it, add the contents of column 2 and 3 on that row to a running total for all of the occurances found. Ex:ample: | 1 | 2 | 3 ---------------------------------- A | Gregory Day | 2 | 2 B | Gregory Day | 2 | 2 C | Gregory Day | 2 | 2 Should return an answer of 12. Does that make sense? The only additional question is that the formula would be a sheet 1, the data on sheet 2. I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations there of, with out luck. Any help would be greatly appreciated. Thank you, |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding fileds in a list based on a lookup
THANK YOU! That makes sense. Now, it just returns #NA. I am trying to
incorporate ISNA in to the formula you provided, but can't seem to make it work. Any suggestions on that? -Thanks again. "sahafi" wrote: Try this version of SUMPRODUCT: =SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5)) Assuming the data is in Sheet2 A1:C5 and the formula goes into Sheet2 per your request. H.T.H. -- when u change the way u look @ things, the things u look at change. "Gregory Day" wrote: I have a sheet containing 3 columns. The first column is a name, the second and third are numbers. I would like to have a foumula that will look down the list for each occurance of a specific name, and upon finding it, add the contents of column 2 and 3 on that row to a running total for all of the occurances found. Ex:ample: | 1 | 2 | 3 ---------------------------------- A | Gregory Day | 2 | 2 B | Gregory Day | 2 | 2 C | Gregory Day | 2 | 2 Should return an answer of 12. Does that make sense? The only additional question is that the formula would be a sheet 1, the data on sheet 2. I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations there of, with out luck. Any help would be greatly appreciated. Thank you, |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding fileds in a list based on a lookup
After working with this I found something I failed to mention. Not all rows
contain the reference criteria. In the example below, if the reference is "Gregory Day" then the answer returned should be 8. How do I make Excel add columns 2 and 3 to a running total for only the rows that contain the reference in column 1? | 1 | 2 | 3 ---------------------------------- A | Gregory Day | 2 | 2 B | John Doe | 2 | 2 C | Gregory Day | 2 | 2 Thank you so much! All your help is greatly appreciated. "sahafi" wrote: Try this version of SUMPRODUCT: =SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5)) Assuming the data is in Sheet2 A1:C5 and the formula goes into Sheet2 per your request. H.T.H. -- when u change the way u look @ things, the things u look at change. "Gregory Day" wrote: I have a sheet containing 3 columns. The first column is a name, the second and third are numbers. I would like to have a foumula that will look down the list for each occurance of a specific name, and upon finding it, add the contents of column 2 and 3 on that row to a running total for all of the occurances found. Ex:ample: | 1 | 2 | 3 ---------------------------------- A | Gregory Day | 2 | 2 B | Gregory Day | 2 | 2 C | Gregory Day | 2 | 2 Should return an answer of 12. Does that make sense? The only additional question is that the formula would be a sheet 1, the data on sheet 2. I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations there of, with out luck. Any help would be greatly appreciated. Thank you, |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding fileds in a list based on a lookup
Here's how you add the ISNA to trap the error:
=IF(ISNA(SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5))),"",SUMPROD UCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5))) H.T.H. -- when u change the way u look @ things, the things u look at change. "Gregory Day" wrote: THANK YOU! That makes sense. Now, it just returns #NA. I am trying to incorporate ISNA in to the formula you provided, but can't seem to make it work. Any suggestions on that? -Thanks again. "sahafi" wrote: Try this version of SUMPRODUCT: =SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5)) Assuming the data is in Sheet2 A1:C5 and the formula goes into Sheet2 per your request. H.T.H. -- when u change the way u look @ things, the things u look at change. "Gregory Day" wrote: I have a sheet containing 3 columns. The first column is a name, the second and third are numbers. I would like to have a foumula that will look down the list for each occurance of a specific name, and upon finding it, add the contents of column 2 and 3 on that row to a running total for all of the occurances found. Ex:ample: | 1 | 2 | 3 ---------------------------------- A | Gregory Day | 2 | 2 B | Gregory Day | 2 | 2 C | Gregory Day | 2 | 2 Should return an answer of 12. Does that make sense? The only additional question is that the formula would be a sheet 1, the data on sheet 2. I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations there of, with out luck. Any help would be greatly appreciated. Thank you, |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding fileds in a list based on a lookup
I'm not sure I understood what you are asking. But the way SUMPRODUCT formula
works, it will look up the criteria (your name for example), then it will add up all the coresponding numbers for that name on both columns 2 and 3. So the formula is already doing that. If the criteria you are using to look up the reference in Col1 doesn't exist, you will recieve that error (previous message explained how to trap the error). So unless you clarify what you are trying to accomplish, we will not be able to help you in that regard. Just remember that on the sheet that contains the formula, you list the same reference (only once) you have on col1 of your data sheet. -- when u change the way u look @ things, the things u look at change. "Gregory Day" wrote: After working with this I found something I failed to mention. Not all rows contain the reference criteria. In the example below, if the reference is "Gregory Day" then the answer returned should be 8. How do I make Excel add columns 2 and 3 to a running total for only the rows that contain the reference in column 1? | 1 | 2 | 3 ---------------------------------- A | Gregory Day | 2 | 2 B | John Doe | 2 | 2 C | Gregory Day | 2 | 2 Thank you so much! All your help is greatly appreciated. "sahafi" wrote: Try this version of SUMPRODUCT: =SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5)) Assuming the data is in Sheet2 A1:C5 and the formula goes into Sheet2 per your request. H.T.H. -- when u change the way u look @ things, the things u look at change. "Gregory Day" wrote: I have a sheet containing 3 columns. The first column is a name, the second and third are numbers. I would like to have a foumula that will look down the list for each occurance of a specific name, and upon finding it, add the contents of column 2 and 3 on that row to a running total for all of the occurances found. Ex:ample: | 1 | 2 | 3 ---------------------------------- A | Gregory Day | 2 | 2 B | Gregory Day | 2 | 2 C | Gregory Day | 2 | 2 Should return an answer of 12. Does that make sense? The only additional question is that the formula would be a sheet 1, the data on sheet 2. I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations there of, with out luck. Any help would be greatly appreciated. Thank you, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with calculating data from different fileds | Excel Discussion (Misc queries) | |||
| Queries formatting fileds | Excel Discussion (Misc queries) | |||
| Making 2 similar fileds in form mirrir of each other. | Excel Discussion (Misc queries) | |||
| Adding a subset of rows of cells, SUM, based on Lookup | Excel Discussion (Misc queries) | |||
| Hide rows and update fileds in different sheets | Excel Discussion (Misc queries) | |||
Linear Mode
