Counting 22 countries in one formula

D

DKerr

Hi,

I have a spreadsheet where cells B3:B850 show names of countries. I
have a list of 22 priority nations and would like a formula to count
the total number of occurances. I can count it my counting each of the
22 nations and adding this together, but can this be done in a single
formula? I am using Excel 97.

Thanks for any assistance.
 
M

Max

One way ..

Assuming the priority list of 22 nations is in D1:D22,
try in say, E1:
=SUMPRODUCT(--ISNUMBER(MATCH(B3:B850,D1:D22,0)))
 
D

DKerr

Works perfectly, but I have another issue, when I was trying to do this
myself I was trying to modify an array formula I use elsewhere in the
spreadsheet

=SUM(IF($B$3:$B$850='Static data'!$G$7,IF($O$3:$O$850=3,1,0)))


I tried to change "$G$7" to "$G$7:$G29". Using your sumproduct formula
how do I incorporate the

"IF($O$3:$O$850=3,1,0)))" bit,

The O column has the week number so the above formula would count for
week 3.
 
D

daddylonglegs

Try

=SUMPRODUCT(--ISNUMBER(MATCH(B3:B850,'static
data'!G7:G29,0)),--(O3:O850=3))
 
M

Max

Not sure why, but daddylonglegs's suggestion should have worked

Another variation:
=SUMPRODUCT((ISNUMBER(MATCH(B3:B850,'Static data'!G7:G28,0))*(O3:O850=3)))

Above carries a slight amendment to the range: G7:G28 (to correspond to 22
cells)
 
D

DKerr

My mistake,

Column O contains a formula to calculate the week number like in cell
O3

=VLOOKUP(F3,'Static data'!$B$4:$C$34,2,FALSE)

This showed an #N/A value in rows that were imcomplete, this caused the
#N/A value. I've changed the VLOOKUP to

=IF($F3=0,"",VLOOKUP(F3,'Static data'!$B$4:$C$34,2,FALSE))

To remove the #N/A value, and everything is fine, thanks all for the
help.
 
D

DKerr

My mistake,

Column O contains a formula to calculate the week number like in cell
O3

=VLOOKUP(F3,'Static data'!$B$4:$C$34,2,FALSE)

This showed an #N/A value in rows that were imcomplete, this caused the
#N/A value. I've changed the VLOOKUP to

=IF($F3=0,"",VLOOKUP(F3,'Static data'!$B$4:$C$34,2,FALSE))

To remove the #N/A value, and everything is fine, thanks all for the
help.
 
Top