Countifs / create columns for each attribute with datatype 1/0

Copper Contributor

Hey guys,

I am not sure if the subject ist understandable...

I hope you can help with my topic. I never heard from COUNTIFs before, but heard that my "problem" can be solved with this excel function..

 

I try it to describe with the attached screenshots.

 

I want "transfer" this list

one user with all musicgenres in one colums seperated by semikolon.png

 

into such one:

one user is or is not a member of the musicgenre .png

 

Is this possible?

Thank you very much for your help in advance!

Kind regards!

4 Replies

Hi,

 

This should solve your problem, =IF(IFERROR(FIND(B$1,$A$3,1),0),1,0)  see attached

 

 

Wow, great! Thank you very much! Will try it out later.

 

But could it be that when I have e.g

Memberlist: Deep-House; House; Techno; 

that this formula can not "spilt" the two genres in its own cells with 1/0, because the formula can not distinguish the word House? (Deep-House; House;) 

You know what I mean?

Kind regards.

If you changed the formula to =IF(IFERROR(FIND("/" & B$1,$A$3,1),0),1,0), it wouldn't work for the first entry (no forward slash) but would for everything else.

Thank you very much for your help again.

I just want to let you know that following (german) code worked for me:

 

=ISTZAHL(FINDEN("#"&M$1&"#";"#"&WECHSELN($G20;" / ";"#")&"#"))*1

 

Cheers!