Excel formula - Counting problem

  • Thread starter Thread starter paulJJ
  • Start date Start date
P

paulJJ

I have 3 columns, one (A) is a list of countries, the second (B) is a
list of the continents they are in and the final (C) is the years in
which that country was visited.

I want to be able to count the (B) column only if it has a year in the
(C) column. Eg there might be 100 countries in column (A), 20 of which
are in Europe(indicated in column (B) but only 10 have a year next to
them in column (C) I want to work out in a seperate cell how many
European countries have been visited .....hope that makes sense!!!
 
One way:

=SUMPRODUCT((B1:B20="Europe")*(LEN(C1:C20)>0))

Extend the ranges if needed.

HTH
Jason
Atlanta, GA
 
One way:

=SUMPRODUCT(--(B1:B1000="Europe"),--(C1:C1000<>""))

or, if Column C might have space characters:

=SUMPRODUCT(--(B1:B1000="Europe"),--(LEN(TRIM(C1:C1000))>0))
 
Thanks everyone for taking the time and replying they have all been ver
helpful

Cheers

Paul:
 
Back
Top