Forum Discussion

salti790's avatar
salti790
Copper Contributor
Feb 20, 2019

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

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

 

into such one:

 

Is this possible?

Thank you very much for your help in advance!

Kind regards!

COUNTIFS
THR1019

4 Replies

Sort By
  • Rich99's avatar
    Rich99
    Iron Contributor
    Feb 20, 2019

    Hi,

     

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

     

     

    • Rich99's avatar
      Rich99
      Iron Contributor
      Feb 20, 2019
      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.
      • salti790's avatar
        salti790
        Copper Contributor
        Feb 25, 2019

        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!

    • salti790's avatar
      salti790
      Copper Contributor
      Feb 20, 2019

      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.

Resources