Counting values in column B in condition of A

[Nrechnitzer]

I have a sheet with a country column and a column with numbers for each
country. I need a count for a range of values belonging to a specific
country. E.g. "For each country, count all cells with a value between 1 and
5". I am using Excel 2003

 

[Per Jessen]

Hi

You need a Sumproduct formula. With country and numbers in A:B and the
country to count for in D1, try this (the numbers 1 and 5 can be
substituted with a cell reference):

=SUMPRODUCT(--($A$1:$A$100=D1),--($B$1:$B$100>=1),--($B$1:$B$100<=5))

Regards,
Per

 

[Francis]

To count the number of occurences for a specific country between the
value 1 to 5, try this
=SUMPRODUCT(--(A2:A100=C2),--(B2:B100>=1),--(B2:B100<=5)), where C2 is the
cell to hold the country

to sum the values between 1 to 5 for a country, try this
=SUMPRODUCT(--(A2:A100=C2),--(B2:B100>=1),--(B2:B100<=5),B2:B100)
where C2 is the cell to hold the country

In 2007 version, you may try SUMIFS
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another