Counting 22 countries in one formula
 

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.


--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=515253

Counting 22 countries in one formula
 

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)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DKerr" wrote in
message ...

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.


--
DKerr
------------------------------------------------------------------------
DKerr's Profile:
http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=515253

Counting 22 countries in one formula
 

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.


--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=515253

Counting 22 countries in one formula
 

Try

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=515253

Counting 22 countries in one formula
 

daddylonglegs Wrote:
Try

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

I get an #N/A when I try that.


--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=515253

Counting 22 countries in one formula
 

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DKerr" wrote in
message ...

daddylonglegs Wrote:
Try

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

I get an #N/A when I try that.


--
DKerr
------------------------------------------------------------------------
DKerr's Profile:
http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=515253

Counting 22 countries in one formula
 

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.


--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=515253

Counting 22 countries in one formula
 

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.


--
DKerr
------------------------------------------------------------------------
DKerr's Profile: http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=515253

Counting 22 countries in one formula
 

Glad you got it sorted out / working !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DKerr" wrote in message
...

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.


--
DKerr
------------------------------------------------------------------------
DKerr's Profile:
http://www.excelforum.com/member.php...o&userid=13087
View this thread: http://www.excelforum.com/showthread...hreadid=515253