"Look up" with multiple hits?
 

There is a range of "look up" formulas that finds a value in one column and
delivers a value from a different column in the same row. But all of them
seemes to be designed to find one specific answer.

I have a column where I mark differnt types of rowes. Let's say there are
ALFA-types and BETA-types. I want Excel to find all the rowes marked ALFA in
column number one (they are not sorted), and add their values from another
column.

This seemes to be so basic that I guess I'm missing something obvious.
Please help me out.

Cheers

/Olle

"Look up" with multiple hits?
 

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=SUM(VLookups("ALFA",dataRange,3))

for the sum of the values in the 3rd column of the range named
"dataRange" corresponding to ALFA in the first column.

Alan Beban

Olle Nyström wrote:
There is a range of "look up" formulas that finds a value in one column and
delivers a value from a different column in the same row. But all of them
seemes to be designed to find one specific answer.

I have a column where I mark differnt types of rowes. Let's say there are
ALFA-types and BETA-types. I want Excel to find all the rowes marked ALFA in
column number one (they are not sorted), and add their values from another
column.

This seemes to be so basic that I guess I'm missing something obvious.
Please help me out.

Cheers

/Olle

"Look up" with multiple hits?
 

Thank you for your quick responce.

Sorry, but that only gives me the value of the first hit, not all of the
hits that's marked ALFA. This, and similar things, is what I've been trying
all day. I'm getting crazy :-)

/Olle


"Alan Beban" skrev i meddelandet
...
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=SUM(VLookups("ALFA",dataRange,3))

for the sum of the values in the 3rd column of the range named
"dataRange" corresponding to ALFA in the first column.

Alan Beban

Olle Nyström wrote:
There is a range of "look up" formulas that finds a value in one column
and
delivers a value from a different column in the same row. But all of
them
seemes to be designed to find one specific answer.

I have a column where I mark differnt types of rowes. Let's say there
are
ALFA-types and BETA-types. I want Excel to find all the rowes marked
ALFA in
column number one (they are not sorted), and add their values from
another
column.

This seemes to be so basic that I guess I'm missing something obvious.
Please help me out.

Cheers

/Olle

"Look up" with multiple hits?
 

And the answer is...

=SUMIF(ConditionRange,"ALFA",RangeToSum)

I got it from another group. Thank you!

/Olle


"Olle Nyström" skrev i meddelandet
...
Thank you for your quick responce.

Sorry, but that only gives me the value of the first hit, not all of the
hits that's marked ALFA. This, and similar things, is what I've been
trying
all day. I'm getting crazy :-)

/Olle


"Alan Beban" skrev i meddelandet
...
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=SUM(VLookups("ALFA",dataRange,3))

for the sum of the values in the 3rd column of the range named
"dataRange" corresponding to ALFA in the first column.

Alan Beban

Olle Nyström wrote:
There is a range of "look up" formulas that finds a value in one
column
and
delivers a value from a different column in the same row. But all of
them
seemes to be designed to find one specific answer.

I have a column where I mark differnt types of rowes. Let's say there
are
ALFA-types and BETA-types. I want Excel to find all the rowes marked
ALFA in
column number one (they are not sorted), and add their values from
another
column.

This seemes to be so basic that I guess I'm missing something obvious.
Please help me out.

Cheers

/Olle