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!

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!

=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

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!

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 -

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 -

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 -

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.