how,,

[pierre]

PLEASE guys
i have 2 questions

first issue)

given :

A
1 Z
2 ZZ
3 ZZ

i am entering the following formula : =COUNTIF($A$1:$A$3;"*"&C1&"*")
i mean that if i should type the "z" letter in search string C1 i should
get =1
but this formula is giving me =3 .what to do ?


2nd issue)

given :

A
1 pen a
2 pen b
3 pen c
4 table a
5 table B

my goal here is to type in search string B1 the world "pen" in order to get
all results related to " pen " like (pen a , pen b , pen c)
so i am doing the following :

in C1 : =COUNTIF(A1:A5;"*"&C1&"*")

in D1 :
{=IF(ROWS(D$22)<=D$1;INDEX(A$1:A$5;SMALL(IF(ISNUMBER(SEARCH(C1;A$1:A$5));ROW(A$1:A$5));ROWS(D$22)));"")}....AND I DRAG DOWN

NOW MY QUESTION HERE :

WHAT SHOULD I DO IN ORDER TO TYPE THE WORD "PEN" IN SEARCH STRING CELL C1
AND THE WORD "TABLE" IN SEARCH STRING CELL C2 IN ORDER TO GET ALL THEIR
CORESPONDENT RESULTS ????

PLEASE HELP

 

[KePaHa]

For your 1st question, the 2nd argument should be simply C1, and not *& C1 &*
I think.

 

[Fred Smith]

1. =COUNTIF($A$1:$A$3;C1)
2. =COUNTIF(A1:A5;"*"&C1&"*")+=COUNTIF(A1:A5;"*"&C2&"*")

Regards
Fred