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 -

On Aug 19, 9:16 am, 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 -

Well, simple text references are just like value references, like
"=A1". But if your data is as simple as you've shown, maybe, in H2:
IF(B2="x",B$1,"-")

Copy across and down. But if the database is more complex, you may
want something trickier, like an OFFSET function inside the IF.

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

Thanks Lars for your question. However I am not sure this going to solve my
problem.

My range is going from CL01 to CL45. So I think an array formula would be
helpful.

* The output (OP1, OP2, OP3) is a maximum of 3 choices.
* To make it more difficult: if I also want to put "y" in my input table,
and a new output for these "y": OPY1, OPY2, OPY3...

Can anyone help me with this?

"Lars-Ã…ke Aspelin" wrote:

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