Sum using named range as criteria

**adyan76** · 10-05-2011, 01:50 PM

Hi Experts,
I am not new to Excel but I am currently not able to solve a prob.
I have a set of data with countries in the column A and in the column B the values. These values should be summed based on countries present in a named range list.

Could someone give me a hint directing toward the solution.
I am using Windows Xp Prof. and Office 2003

Thanks in advance

**NBVC** · 10-05-2011, 02:05 PM

If I understand correctly, say you have a list of countries in your named range, called Countries... and you have a list of countries in A1:A5 which may include the countries in the named range and may include others not in the range. You want to sum column B only if the countries in column A are in your named range, Countries. Yes?

Try:

=SUMPRODUCT(SUMIF(A2:A5,Countries,B2:B5))

or

=SUMPRODUCT(SUMIF(A2:A5;Countries;B2:B5))

**adyan76** · 10-06-2011, 02:16 AM

Originally Posted by NBVC

If I understand correctly, say you have a list of countries in your named range, called Countries... and you have a list of countries in A1:A5 which may include the countries in the named range and may include others not in the range. You want to sum column B only if the countries in column A are in your named range, Countries. Yes?

Try:

=SUMPRODUCT(SUMIF(A2:A5,Countries,B2:B5))

or

=SUMPRODUCT(SUMIF(A2:A5;Countries;B2:B5))

Thanks worked like a waterfall.