filters + countif statements

[mike.lewis]

Hi there

i have a sheet with information on it, in one column the date could look like this

**
*73*
23**

the length of these numbers can vary

what i would like to find out in a sheet of over 30,000 entries is how many have the sequence *(numbers)* or ** or (numbers)**, i have tried through the excel 2010 advanced filter options but i dont get very far as it still contains all sorts of mixes

any ideas

 

[Aladin Akyurek]

Hi there

i have a sheet with information on it, in one column the date could look like this

**
*73*
23**

the length of these numbers can vary

what i would like to find out in a sheet of over 30,000 entries is how many have the sequence *(numbers)* or ** or (numbers)**, i have tried through the excel 2010 advanced filter options but i dont get very far as it still contains all sorts of mixes

any ideas

Control+shift+enter, not just enter:

=SUM(IF(SUBSTITUTE($A$2:$A$100,"*","")="",IF(LEN($A$2:$A$100)=2,1)))

counts the occurrences of the ** pattern that occurs stand-alone.

=SUM(
         IF(LEN($A$1:$A$100)>2,
         IF(ISNUMBER(REPLACE(LEFT(SUBSTITUTE($A$1:$A$100,"*","#"),
                  LEN($A$1:$A$100)-1),1,1,"")+0),
         IF(LEFT($A$1:$A$100)="*",
         IF(RIGHT($A$1:$A$100)="*",1)))))

counts the occurences of the *N* pattern where N stands for a number.

=SUM(
     IF(LEN($A$1:$A$100)>2,
     IF(ISNUMBER(LEFT(SUBSTITUTE($A$1:$A$100,"*","#"),
         LEN($A$1:$A$100)-2)+0),
     IF(RIGHT($A$1:$A$100,2)="**",1))))

counts the occurences of the N** pattern where N stands for a number.

Note that * is taken above literally.