| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to know the number of times "fam" appears in a list of cells
In a large census of data cell A1 through cell A1000; some of these cells
contain the letters "fam" some do not, without have to count, is there a formula? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to know the number of times "fam" appears in a list of cells
Try this
=COUNTIF(A1:A1000,"=fam") "medpay" wrote: In a large census of data cell A1 through cell A1000; some of these cells contain the letters "fam" some do not, without have to count, is there a formula? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to know the number of times "fam" appears in a list of cells
One way:
Assuming only interested in one count per cell: =COUNTIF(A1:A1000,"*fam*") If want to count multiple instances of "fam" within a cell: =SUM(LEN(A1:A1000),-LEN(SUBSTITUTE(A1:A1000,"fam","")))/LEN("fam") In article , medpay wrote: In a large census of data cell A1 through cell A1000; some of these cells contain the letters "fam" some do not, without have to count, is there a formula? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to know the number of times "fam" appears in a list of cells
If "fam is the only thing in the cells, either of the two two methods
proposed will work. if Fam is a portion of the cell entry try =sum(if(len(A:A)-len(substitute(A:A,"Fam",""))1,1,0)) etered as an array control-shift-enter "medpay" wrote: In a large census of data cell A1 through cell A1000; some of these cells contain the letters "fam" some do not, without have to count, is there a formula? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to know the number of times "fam" appears in a list of cells
Should have mentioned that this needs to be array-entered...
In article , JE McGimpsey wrote: If want to count multiple instances of "fam" within a cell: =SUM(LEN(A1:A1000),-LEN(SUBSTITUTE(A1:A1000,"fam","")))/LEN("fam") |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to know the number of times "fam" appears in a list of cells
Be careful using formulas with SUBSTITUTE as this function is case-sensitive
and "fam" is not equal to "Fam" "medpay" wrote in message ... In a large census of data cell A1 through cell A1000; some of these cells contain the letters "fam" some do not, without have to count, is there a formula? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to know the number of times "fam" appears in a list of cells
That can be easily fixed
=SUMPRODUCT(LEN(A1:A1000)-LEN(SUBSTITUTE(LOWER(A1:A1000),"fam","")))/LEN("fam") however maybe the OP was specific enough to want fam and not Fam or FAM -- Regards, Peo Sjoblom "Tyro" wrote in message ... Be careful using formulas with SUBSTITUTE as this function is case-sensitive and "fam" is not equal to "Fam" "medpay" wrote in message ... In a large census of data cell A1 through cell A1000; some of these cells contain the letters "fam" some do not, without have to count, is there a formula? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| cannot use "Create List" and "Share Workbook" same time | Excel Discussion (Misc queries) | |||
| counting the number of times value of "x" appears in a row | Excel Discussion (Misc queries) | |||
| How can I count the # of times the word "Yes" appears in a range | Excel Worksheet Functions | |||
| Duplicate entry "x" number of times | Excel Discussion (Misc queries) | |||
| How do I add times that were creted with "=TEXT(A2-A1,"h:mm")"? | Excel Worksheet Functions | |||
Linear Mode
