If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VLOOKUP - columnar sheet with multiple matches - need to return a
I have a huge spreadhset (and a deadline!) that VLOOKUP will find several
matches for the lookup_value. Each may have a diferent value at the range_lookup. I need the formula to return a sum of all values. Please help me figure out how to do it! Thanks very much! |
#2
|
|||
|
|||
VLOOKUP - columnar sheet with multiple matches - need to return a
Have you looked at SUMIF ?
Example: =SUMIF(range_that_might_match,criteria,range_to_su m) where range_that_might_match will be the first column of your table, criteria is the lookup_value, and range_to_sum is the column you want to sum where there is a match. Hope this helps. Pete On Sep 21, 10:53*pm, dawgfan wrote: I have a huge spreadhset (and a deadline!) that VLOOKUP will find several matches for the lookup_value. Each may have a diferent value at the range_lookup. I need the formula to return a sum of all values. Please help me figure out how to do it! Thanks very much! |
#3
|
|||
|
|||
VLOOKUP - columnar sheet with multiple matches - need to return a
=sumif(sheet2!a:a,x99,sheet2!b:b)
Will sum all the values in column B of sheet2 where the value in column A of sheet2 matches the value in x99. dawgfan wrote: I have a huge spreadhset (and a deadline!) that VLOOKUP will find several matches for the lookup_value. Each may have a diferent value at the range_lookup. I need the formula to return a sum of all values. Please help me figure out how to do it! Thanks very much! -- Dave Peterson |
#4
|
|||
|
|||
VLOOKUP - columnar sheet with multiple matches - need to return a
Wow, 2 quick posts with the same answer. Great minds think alike.
Unfortunately, I am not getting my data to cooperate. Perhaps I can be more specific: I have two tabs in one workbook. The first tab contains columnar data with the left-most column containing a location. Several columns over, it also contains a column I want to contain the sum of all equipment associated with a given location. The second tab contains columnar data with the left-most column containing locations also; however, each location is listed multiple times - once for each type of equipment located there. The first tab needs to capture the total cost for each location, which consists of multiple lines that are various costs. Does this help? "dawgfan" wrote: I have a huge spreadhset (and a deadline!) that VLOOKUP will find several matches for the lookup_value. Each may have a diferent value at the range_lookup. I need the formula to return a sum of all values. Please help me figure out how to do it! Thanks very much! |
#5
|
|||
|
|||
VLOOKUP - columnar sheet with multiple matches - need to return a
Suppose in your tab1 (sheet1) you have locations in column A,
equipment in column E and quantities in column H. Suppose in your tab2 (sheet2) that you have locations in column A, equipment in column B and you want the sum in column C. Assume 100 rows of data on sheet1 with a header row in row 1 on both sheets. Put this in C2 of Sheet2: =SUMPRODUCT(--(A2=Sheet1!A$2:A$100),--(B2=Sheet1!E$2:E$100),Sheet1!H $2:H$100) Adjust the ranges to suit your data, then copy down. Hope this helps. Pete On Sep 22, 12:07*am, dawgfan wrote: Wow, 2 quick posts with the same answer. Great minds think alike. Unfortunately, I am not getting my data to cooperate. Perhaps I can be more specific: I have two tabs in one workbook. The first tab contains columnar data with the left-most column containing a location. Several columns over, it also contains a column I want to contain the sum of all equipment associated with a given location. The second tab contains columnar data with the left-most column containing locations also; however, each location is listed multiple times - once for each type of equipment located there. The first tab needs to capture the total cost for each location, which consists of multiple lines that are various costs. Does this help? "dawgfan" wrote: I have a huge spreadhset (and a deadline!) that VLOOKUP will find several matches for the lookup_value. Each may have a diferent value at the range_lookup. I need the formula to return a sum of all values. Please help me figure out how to do it! Thanks very much!- Hide quoted text - - Show quoted text - |
#6
|
|||
|
|||
VLOOKUP - columnar sheet with multiple matches - need to retur
Thanks very much for all of your efforts. Still not hitting the right combo.
I need to check column A in tab 1, then check all of column A in tab 2 to see if there are any matches. If any matches are found, then return a value in column B if tab 2 into column B of tab 1. Also, if there are more than one matches in tab 2, then return a total or sum inot tab 1. Simple, right? My hair is getting greyer by the minute. "Pete_UK" wrote: Suppose in your tab1 (sheet1) you have locations in column A, equipment in column E and quantities in column H. Suppose in your tab2 (sheet2) that you have locations in column A, equipment in column B and you want the sum in column C. Assume 100 rows of data on sheet1 with a header row in row 1 on both sheets. Put this in C2 of Sheet2: =SUMPRODUCT(--(A2=Sheet1!A$2:A$100),--(B2=Sheet1!E$2:E$100),Sheet1!H $2:H$100) Adjust the ranges to suit your data, then copy down. Hope this helps. Pete On Sep 22, 12:07 am, dawgfan wrote: Wow, 2 quick posts with the same answer. Great minds think alike. Unfortunately, I am not getting my data to cooperate. Perhaps I can be more specific: I have two tabs in one workbook. The first tab contains columnar data with the left-most column containing a location. Several columns over, it also contains a column I want to contain the sum of all equipment associated with a given location. The second tab contains columnar data with the left-most column containing locations also; however, each location is listed multiple times - once for each type of equipment located there. The first tab needs to capture the total cost for each location, which consists of multiple lines that are various costs. Does this help? "dawgfan" wrote: I have a huge spreadhset (and a deadline!) that VLOOKUP will find several matches for the lookup_value. Each may have a diferent value at the range_lookup. I need the formula to return a sum of all values. Please help me figure out how to do it! Thanks very much!- Hide quoted text - - Show quoted text - |
#7
|
|||
|
|||
VLOOKUP - columnar sheet with multiple matches - need to retur
Well, the formula should do that if you change the cell references to
match your setup. It's getting late here, but perhaps you can give some example data and someone else might be able to come up with something for you. Pete On Sep 22, 1:45*am, dawgfan wrote: Thanks very much for all of your efforts. Still not hitting the right combo. I need to check column A in tab 1, then check all of column A in tab 2 to see if there are any matches. If any matches are found, then return a value in column B if tab 2 into column B of tab 1. Also, if there are more than one matches in tab 2, then return a total or sum inot tab 1. Simple, right? My hair is getting greyer by the minute. "Pete_UK" wrote: Suppose in your tab1 (sheet1) you have locations in column A, equipment in column E and quantities in column H. Suppose in your tab2 (sheet2) that you have locations in column A, equipment in column B and you want the sum in column C. Assume 100 rows of data on sheet1 with a header row in row 1 on both sheets. Put this in C2 of Sheet2: =SUMPRODUCT(--(A2=Sheet1!A$2:A$100),--(B2=Sheet1!E$2:E$100),Sheet1!H $2:H$100) Adjust the ranges to suit your data, then copy down. Hope this helps. Pete On Sep 22, 12:07 am, dawgfan wrote: Wow, 2 quick posts with the same answer. Great minds think alike. Unfortunately, I am not getting my data to cooperate. Perhaps I can be more specific: I have two tabs in one workbook. The first tab contains columnar data with the left-most column containing a location. Several columns over, it also contains a column I want to contain the sum of all equipment associated with a given location. The second tab contains columnar data with the left-most column containing locations also; however, each location is listed multiple times - once for each type of equipment located there. The first tab needs to capture the total cost for each location, which consists of multiple lines that are various costs. Does this help? "dawgfan" wrote: I have a huge spreadhset (and a deadline!) that VLOOKUP will find several matches for the lookup_value. Each may have a diferent value at the range_lookup. I need the formula to return a sum of all values. Please help me figure out how to do it! Thanks very much!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
|
|||
|
|||
VLOOKUP - columnar sheet with multiple matches - need to return a
On Mon, 21 Sep 2009 16:07:01 -0700, dawgfan
wrote: Wow, 2 quick posts with the same answer. Great minds think alike. Unfortunately, I am not getting my data to cooperate. Perhaps I can be more specific: I have two tabs in one workbook. The first tab contains columnar data with the left-most column containing a location. Several columns over, it also contains a column I want to contain the sum of all equipment associated with a given location. The second tab contains columnar data with the left-most column containing locations also; however, each location is listed multiple times - once for each type of equipment located there. The first tab needs to capture the total cost for each location, which consists of multiple lines that are various costs. Does this help? "dawgfan" wrote: I have a huge spreadhset (and a deadline!) that VLOOKUP will find several matches for the lookup_value. Each may have a diferent value at the range_lookup. I need the formula to return a sum of all values. Please help me figure out how to do it! Thanks very much! Just counting the total number of equipment items at a location will create an ambiguity if there is more than one price for the items being tallied. |
Thread Tools | |
Display Modes | |
|
|