Matching values between array and matrix

Hi everyone,

I'm not sure if this is simple and I'm just complicating things but here it goes.

I have this raw data format where each column represents a variable (there are many but I'll just write one for simplicity) and each row an occurrence (it's actually a respondent in a survey)
My objective is to count how many a's and b's and so on exist in each variable

variable 1

a,b,c,d

b,d

a,b,d

So I thought that maybe I could create 4 columns under variable 1 with headers a b c and d, perform a text to columns separated by a comma and then verify if occurance x has any a, b, c or d
The problem is that the a,b,c and d will not match the headers because they are not ordered correctly, so I don't know what kind of function I could apply here!
e.g.:

a	b	c	d
a	b	c	d
b	d
a	b	d

Do you have any suggestion? Sorry but I'm struggling in explaining the problem any better, that's how lost I am.

Hi arupaka and welcome to the forum,

Is the attached example what you need?

CountIf using header match.xlsx

Sorry for off-topic interjection:

Marvin - you should know better!

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

Hey AliGW,

I'm just here to answer questions. I built the sample he didn't know how to do and did what I thought was his answer. I attached the file asking if this was what he meant. I guess I've just seen lots of problems and have an idea of needs? Psychic? If I have time and think I can answer the problem, I'm not adverse to building the data for the OP.

Uncle Marv

Marvin - you should know by now that we expect to see some form of explanation in your post. Please provide more detail of what you have done inside the attachment when you provide one. NOBODY should have to open an attachment to get a sense of the solution offered - some members won't be able to, and threads are not for the sole benefit of the OP, but for the benefit of all. Please comply with this request in future. Thanks.