how can i count text in a ranges of data
 

Hi all,
I have a file contain 10 sheets,every sheet contain list of names,i need to
know the frequncy of certian name all over the 10 sheets.

Thanks

how can i count text in a ranges of data
 

On Tue, 30 Jan 2007 07:40:01 -0800, Abdelmoneim,Mahmoud
wrote:

Hi all,
I have a file contain 10 sheets,every sheet contain list of names,i need to
know the frequncy of certian name all over the 10 sheets.

Thanks

If you will be referring to no more than 65,536 cells, you could download and
install Longre's free and easily distributable add-in morefunc.xll from
http://xcell05.free.fr/

Then use the COUNTIF.3D function.


--ron

how can i count text in a ranges of data
 

If all the sheets have the same configuration (names in identical columns),
you could try something like this:

FIRST - you must create a list of your sheet names.
You can place this list in an out-of-the-way location, say Z1 to Z10.

If the sheet names are XL's default names, just enter
Sheet1
in Z1 and drag down to automatically increment and create the list.

If you're using others sheet names, then key them into Z1 to Z10, making
sure they are spelled exactly as they appear in the tabs.

Let's say that your names are contained In A1 to D20 on each of your 10
sheets.
Assume the name you're looking to count is entered into E1.

Then try this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Z1:Z10&"'!A1:D20" ),E1))

Watch those apostrophes and double quotes!
They're included just in case there might be spaces in the sheet names.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===




"Abdelmoneim,Mahmoud" wrote
in message ...
Hi all,
I have a file contain 10 sheets,every sheet contain list of names,i need to
know the frequncy of certian name all over the 10 sheets.

Thanks