finding a number and the number of times it occurs
 

I am completely new to Excel. Please have mercy :)

I have a table of numbers (40 columns by 10 rows). In each cell is one
number.

I want to analyze the table of numbers for a given number and indicate
a "0" for no matches and the count if there is 1 or more matches.

Example using smaller table of data:

Say A1 to E2 is as follows:
1 3 9 5 20
5 4 2 5 5

I want A5 (the cell with the formula) to examine A1 to E2 for "5."
Since there are 4 occurances of "5" I want A5 to display "4." Likewise,
if I want A6 to examince A1 to E2 for "99" I want A6 to display "0" as
there are no 99s present in A1 to E2.

What formula should I use for such a purpose as this?

Thanks in advance :)


--
luposlipophobia
------------------------------------------------------------------------
luposlipophobia's Profile: http://www.excelforum.com/member.php...o&userid=35655
View this thread: http://www.excelforum.com/showthread...hreadid=554391

finding a number and the number of times it occurs
 

=COUNTIF(A1:E2,5)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"luposlipophobia"
<luposlipophobia.29s800_1150943404.6614@excelfor um-nospam.com wrote in
message news:luposlipophobia.29s800_1150943404.6614@excelf orum-nospam.com...

I am completely new to Excel. Please have mercy :)

I have a table of numbers (40 columns by 10 rows). In each cell is one
number.

I want to analyze the table of numbers for a given number and indicate
a "0" for no matches and the count if there is 1 or more matches.

Example using smaller table of data:

Say A1 to E2 is as follows:
1 3 9 5 20
5 4 2 5 5

I want A5 (the cell with the formula) to examine A1 to E2 for "5."
Since there are 4 occurances of "5" I want A5 to display "4." Likewise,
if I want A6 to examince A1 to E2 for "99" I want A6 to display "0" as
there are no 99s present in A1 to E2.

What formula should I use for such a purpose as this?

Thanks in advance :)


--
luposlipophobia
------------------------------------------------------------------------
luposlipophobia's Profile:
http://www.excelforum.com/member.php...o&userid=35655
View this thread: http://www.excelforum.com/showthread...hreadid=554391

finding a number and the number of times it occurs
 

You can use the Countif() function.

=COUNTIF(A1:E2,5)

Also, instead of 'hardcoding' the criteria ( 5 ) into the formula itself,
you could have the formula reference a particular cell where you enter the
number to count, without having to revise the actual formula itself.

Say you enter your criteria in B5.

=COUNTIF(A1:E2,B5)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"luposlipophobia"
<luposlipophobia.29s800_1150943404.6614@excelfor um-nospam.com wrote in
message news:luposlipophobia.29s800_1150943404.6614@excelf orum-nospam.com...

I am completely new to Excel. Please have mercy :)

I have a table of numbers (40 columns by 10 rows). In each cell is one
number.

I want to analyze the table of numbers for a given number and indicate
a "0" for no matches and the count if there is 1 or more matches.

Example using smaller table of data:

Say A1 to E2 is as follows:
1 3 9 5 20
5 4 2 5 5

I want A5 (the cell with the formula) to examine A1 to E2 for "5."
Since there are 4 occurances of "5" I want A5 to display "4." Likewise,
if I want A6 to examince A1 to E2 for "99" I want A6 to display "0" as
there are no 99s present in A1 to E2.

What formula should I use for such a purpose as this?

Thanks in advance :)


--
luposlipophobia
------------------------------------------------------------------------
luposlipophobia's Profile:
http://www.excelforum.com/member.php...o&userid=35655
View this thread: http://www.excelforum.com/showthread...hreadid=554391

finding a number and the number of times it occurs
 

Thanks so much!!


--
luposlipophobia
------------------------------------------------------------------------
luposlipophobia's Profile: http://www.excelforum.com/member.php...o&userid=35655
View this thread: http://www.excelforum.com/showthread...hreadid=554391