SUMIF for texts
 

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 -

SUMIF for texts
 

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.

SUMIF for texts
 

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

Array formula needed
 

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