On Tue, 19 Aug 2008 07:16:15 -0700, Pieter
wrote:

Could anyone help me with this?

I want to create a reference on text (like a SUMIF, but for text)

input: different persons get "x" on CL01, CL02,...
output via formula: OP1,OP2,OP3 listing the column with "x"

A B C D H I J
1 CL01 CL02 CL03 OP1 OP2 OP3
2 John x CL01 - -
3 Hugues x x CL02 CL03 -

If your data is limited to 3 columns (B to D) you can try the
following formulas:

In cell H2:
=IF(SUMPRODUCT(--(B2:D2="x"))=0,"-",INDEX(B$1:D$1,MATCH("x",B2:D2,0)))

In cell I2:
=CHOOSE(SUMPRODUCT(--(B2:D2="x"))+1,"-","-",CHOOSE(SUMPRODUCT(--(B2:D2=""),COLUMN(B:D)-COLUMN(A:A)),D$1,D$1,C$1),C$1)

In cell J2:
=IF(SUMPRODUCT(--(B2:D2="x"))=3,D$1,"-")

Copy the three cells H2:J2 down as far as you have rows in your data
table

For the general case, with more than 3 columns, something more clever,
probably including array formulas, have to be found.

Hope this helps / Lars-Åke