i get empty cells........there is something wrong







"T. Valko" wrote:

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

That formula needs to be array entered.

Enter this array formula in D2 and copy across to E2:

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<"",$C$1:$C$3)) ,$A$1:$A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")

Select both D2 and E2 then copy down as needed.


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30
4 axe a 40
5 axe b 50
6 axe c 60

my search string cells are : c1 : c3

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$7;SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF(C$1:C$3<"",C$1:C$3)),A$1 :A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")}
and i dragged down...


my problem is : WHAT FORMULA SHOULD I ENTER in cell E2 IN ORDER TO GET THE
CORRESPONDING VALUES of "tree" and "axe" ???

i tried the following :

i enterd in E1 :SUMPRODUCT(COUNTIF(D2:D12,"*"))
and i entered in E1:
{=IF(ROWS(E$2:E2)<=E$1,INDEX(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($B$1:$B$7<"",$B$1:$B$7)) ,B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E2))),"")}
and i dragged down

now if i enter the word "axe" in cell C1 i get the following WRONG
results

10 for axe a
20 for axe b
30 for axe c

HELP