Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a month to a quarter ...... | New Users to Excel | |||
count specific text that occurs in a range of cells | Excel Discussion (Misc queries) | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
count G4:G51 for critera only if D4:D51 contains text data | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |