How to identify multiple product users

[ekirk]

I have a data set where each of our customers can have one or more than one of our 10 products. The data right now is organized like this:

Column Names: Customer name/product 1/product 2/ product 3/....
Entry: Emma/Y/N/N...

I need an easy way to not only see how many people have product 1, but how many of those people have product 1 and 2. I just cannot figure out how to organize my data to help me do this. I've tried pivot tables, but it's just not combining the data in a helpful way.

I'm really looking forward to your advice!

 

[PaddyD]

Yes, but with formulas of the form:

=and(b2="Yes",c2="Yes",d2="Yes")

...which aren't themselves that onerous.

 

[Aladin Akyurek]

Okay, so in the sample I shared all 11 people have product 1. Of those 11 people how many have product 2? How many have product 2 and 3?

What is the count as answer to the first "how many" and what is the count as answer to the second "how many"?

 

[ekirk]

What is the count as answer to the first "how many" and what is the count as answer to the second "how many"?

I don't understand your question. I'm trying to get the data to tell me how many! If I knew I wouldn't be asking the question here.

 

[Aladin Akyurek]

I don't understand your question. I'm trying to get the data to tell me how many! If I knew I wouldn't be asking the question here.

What is obvious to you is not necessarily obvious to others:

You wrote: "Okay, so in the sample I shared all 11 people have product 1. Of those 11 people how many have product 2? How many have product 2 and 3?"

You want a formula that computes the answer to "Of those 11 people how many have product 2?" But you don't reveal the count the formula you ask for should compute. In the same vein, the answer to the question "How many have product 2 and 3" is given...

 

[ekirk]

I'm sorry Aladin. I know you are trying to help me, and I appreciate it, but I don't understand your question.

Here's a sample of my data. 5 lines. 5 people have product 1, 0 people have product 1 & 2, 0 people have product 1 & 3, 0 people have product 1 & 4... so on. 1 person has product 1 and 7. Is there a formula that can tell me this?

[TABLE="width: 960"]
<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: left"]Product 1[/TD]
[TD="width: 64, align: left"]Product 2[/TD]
[TD="width: 64, align: left"]Product 3[/TD]
[TD="width: 64, align: left"]Product 4[/TD]
[TD="width: 64, align: left"]Product 5[/TD]
[TD="width: 64, align: left"]Product 6[/TD]
[TD="width: 64, align: left"]Product 7[/TD]
[TD="width: 64, align: left"]Product 8[/TD]
[TD="width: 64, align: left"]Product 9[/TD]
[TD="width: 64, align: left"]Product 10[/TD]
[TD="width: 64, align: left"]Product 11[/TD]
[TD="width: 64, align: left"]Product 12[/TD]
[TD="width: 64, align: left"]Product 13[/TD]
[TD="width: 64, align: left"]Product 14[/TD]
[TD="width: 64, align: left"]Product 15[/TD]
[/TR]
[TR]
[TD="align: left"]Yes[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[/TR]
[TR]
[TD="align: left"]Yes[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]Yes[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[/TR]
[TR]
[TD="align: left"]Yes[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[/TR]
[TR]
[TD="align: left"]Yes[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[/TR]
[TR]
[TD="align: left"]Yes[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[TD="align: left"]No[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

I'm sorry Aladin. I know you are trying to help me, and I appreciate it, but I don't understand your question.

Here's a sample of my data. 5 lines. 5 people have product 1, 0 people have product 1 & 2, 0 people have product 1 & 3, 0 people have product 1 & 4... so on. 1 person has product 1 and 7. Is there a formula that can tell me this?

[...]

In what follows the sample in post #5 is uded.

A:Q of Sheet1 houses the sample, headers included.

Sheet2 houses the processes (the exhibt below depicts only 5 products: Product1 till product 5.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]

Product 1​

[/td][td]

Product 2​

[/td][td]

Product 3​

[/td][td]

Product 4​

[/td][td]

Product 5​

[/td][/tr]
[tr][td]

2​

[/td][td]

Product 1​

[/td][td]

11​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][/tr]
[tr][td]

3​

[/td][td]

Product 2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]
[tr][td]

4​

[/td][td]

Product 3​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]
[tr][td]

5​

[/td][td]

Product 4​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][/tr]
[tr][td]

6​

[/td][td]

Product 5​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]
[/table]

In B2 enter, copy across, and down:

=COUNTIFS(INDEX(Sheet1!$C$2:$Q$12,0,MATCH($A2,Sheet1!$C$1:$Q$1,0)),"yes",INDEX(Sheet1!$C$2:$Q$12,0,MATCH(B$1,Sheet1!$C$1:$Q$1,0)),"yes")