Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-353659%22%20slang%3D%22en-US%22%3ECountifs%20%2F%20create%20columns%20for%20each%20attribute%20with%20datatype%201%2F0%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353659%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20guys%2C%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20if%20the%20subject%20ist%20understandable...%3C%2FP%3E%3CP%3EI%20hope%20you%20can%20help%20with%20my%20topic.%20I%20never%20heard%20from%20COUNTIFs%20before%2C%20but%20heard%20that%20my%20%22problem%22%20can%20be%20solved%20with%20this%20excel%20function..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20try%20it%20to%20describe%20with%20the%20attached%20screenshots.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20%22transfer%22%20this%20list%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F83632iA149599BA3A9BD23%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22one%20user%20with%20all%20musicgenres%20in%20one%20colums%20seperated%20by%20semikolon.png%22%20title%3D%22one%20user%20with%20all%20musicgenres%20in%20one%20colums%20seperated%20by%20semikolon.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Einto%20such%20one%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F83630i1D5FDD6FC0A029A9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22one%20user%20is%20or%20is%20not%20a%20member%20of%20the%20musicgenre%20.png%22%20title%3D%22one%20user%20is%20or%20is%20not%20a%20member%20of%20the%20musicgenre%20.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20help%20in%20advance!%3C%2FP%3E%3CP%3EKind%20regards!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-353659%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECOUNTIFS%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETHR1019%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-356266%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20%2F%20create%20columns%20for%20each%20attribute%20with%20datatype%201%2F0%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-356266%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much%20for%20your%20help%20again.%3C%2FP%3E%3CP%3EI%20just%20want%20to%20let%20you%20know%20that%20following%20(german)%20code%20worked%20for%20me%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DISTZAHL(FINDEN(%22%23%22%26amp%3BM%241%26amp%3B%22%23%22%3B%22%23%22%26amp%3BWECHSELN(%24G20%3B%22%20%2F%20%22%3B%22%23%22)%26amp%3B%22%23%22))*1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354013%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20%2F%20create%20columns%20for%20each%20attribute%20with%20datatype%201%2F0%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354013%22%20slang%3D%22en-US%22%3EIf%20you%20changed%20the%20formula%20to%20%3DIF(IFERROR(FIND(%22%2F%22%20%26amp%3B%20B%241%2C%24A%243%2C1)%2C0)%2C1%2C0)%2C%20it%20wouldn't%20work%20for%20the%20first%20entry%20(no%20forward%20slash)%20but%20would%20for%20everything%20else.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353889%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20%2F%20create%20columns%20for%20each%20attribute%20with%20datatype%201%2F0%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353889%22%20slang%3D%22en-US%22%3E%3CP%3EWow%2C%20great!%20Thank%20you%20very%20much!%20Will%20try%20it%20out%20later.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20could%20it%20be%20that%20when%20I%20have%20e.g%3C%2FP%3E%3CP%3EMemberlist%3A%20Deep-House%3B%20House%3B%20Techno%3B%26nbsp%3B%3C%2FP%3E%3CP%3Ethat%20this%20formula%20can%20not%20%22spilt%22%20the%20two%20genres%20in%20its%20own%20cells%20with%201%2F0%2C%20because%20the%20formula%20can%20not%20distinguish%20the%20word%20House%3F%20(Deep-House%3B%20House%3B)%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20know%20what%20I%20mean%3F%3C%2FP%3E%3CP%3EKind%20regards.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353870%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20%2F%20create%20columns%20for%20each%20attribute%20with%20datatype%201%2F0%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353870%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20should%20solve%20your%20problem%2C%26nbsp%3B%3DIF(IFERROR(FIND(B%241%2C%24A%243%2C1)%2C0)%2C1%2C0)%20%26nbsp%3Bsee%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
salti790
New 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!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies