software

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: www.excelforum.com/member.php...oamp;userid=13087
View this thread: www.excelforum.com/showthread...hreadid=515253One way ..

Assuming the priority list of 22 nations is in D122,
try in say, E1:
=SUMPRODUCT(--ISNUMBER(MATCH(B3:B850,D122,0)))
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;DKerrquot; gt; wrote in
message news
gt;
gt; Hi,
gt;
gt; I have a spreadsheet where cells B3:B850 show names of countries. I
gt; have a list of 22 priority nations and would like a formula to count
gt; the total number of occurances. I can count it my counting each of the
gt; 22 nations and adding this together, but can this be done in a single
gt; formula? I am using Excel 97.
gt;
gt; Thanks for any assistance.
gt;
gt;
gt; --
gt; DKerr
gt; ------------------------------------------------------------------------
gt; DKerr's Profile:
www.excelforum.com/member.php...oamp;userid=13087
gt; View this thread: www.excelforum.com/showthread...hreadid=515253
gt;

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 quot;$G$7quot; to quot;$G$7:$G29quot;. Using your sumproduct formula
how do I incorporate the

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

The O column has the week number so the above formula would count for
week 3.--
DKerr
------------------------------------------------------------------------
DKerr's Profile: www.excelforum.com/member.php...oamp;userid=13087
View this thread: www.excelforum.com/showthread...hreadid=515253
Try

=SUMPRODUCT(--ISNUMBER(MATCH(B3:B850,'static
data'!G7:G29,0)),--(O3:O850=3))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=515253
daddylonglegs Wrote:
gt; Try
gt;
gt; =SUMPRODUCT(--ISNUMBER(MATCH(B3:B850,'static
gt; data'!G7:G29,0)),--(O3:O850=3))

I get an #N/A when I try that.--
DKerr
------------------------------------------------------------------------
DKerr's Profile: www.excelforum.com/member.php...oamp;userid=13087
View this thread: www.excelforum.com/showthread...hreadid=515253Not 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
savefile.com/projects/236895
xdemechanik
---
quot;DKerrquot; gt; wrote in
message news
gt;
gt; daddylonglegs Wrote:
gt; gt; Try
gt; gt;
gt; gt; =SUMPRODUCT(--ISNUMBER(MATCH(B3:B850,'static
gt; gt; data'!G7:G29,0)),--(O3:O850=3))
gt;
gt; I get an #N/A when I try that.
gt;
gt;
gt; --
gt; DKerr
gt; ------------------------------------------------------------------------
gt; DKerr's Profile:
www.excelforum.com/member.php...oamp;userid=13087
gt; View this thread: www.excelforum.com/showthread...hreadid=515253
gt;

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,quot;quot;,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: www.excelforum.com/member.php...oamp;userid=13087
View this thread: www.excelforum.com/showthread...hreadid=515253
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,quot;quot;,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: www.excelforum.com/member.php...oamp;userid=13087
View this thread: www.excelforum.com/showthread...hreadid=515253Glad you got it sorted out / working !
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;DKerrquot; gt; wrote in message
news
gt;
gt; My mistake,
gt;
gt; Column O contains a formula to calculate the week number like in cell
gt; O3
gt;
gt; =VLOOKUP(F3,'Static data'!$B$4:$C$34,2,FALSE)
gt;
gt; This showed an #N/A value in rows that were imcomplete, this caused the
gt; #N/A value. I've changed the VLOOKUP to
gt;
gt; =IF($F3=0,quot;quot;,VLOOKUP(F3,'Static data'!$B$4:$C$34,2,FALSE))
gt;
gt; To remove the #N/A value, and everything is fine, thanks all for the
gt; help.
gt;
gt;
gt; --
gt; DKerr
gt; ------------------------------------------------------------------------
gt; DKerr's Profile:
www.excelforum.com/member.php...oamp;userid=13087
gt; View this thread: www.excelforum.com/showthread...hreadid=515253
gt;