Formula to count distinct values in specified rows

[Benlafleche]

Dear helpers,

I have an interesting challenge for you!

I the table below:

A	1,2,3,4,5,6,7,8,9,10,11,12
B	2,3,4
C	1,6,12
A	2,6,7
A	1,2,3
C	8,9,5
B	10,2,5,6
A	1,2,3
C	3,4,5
A	6,7,7

<tbody>
</tbody>

I would like to count how many time 1 occurs in the 2nd column only in the rows where the value in the first column is A.

I would like to do this with a formula. No VBA, it's mean to be inserted in an excel tool to be used by excel newbies... Any ideas? I am stuck on this...

Using Vista and Excel 2007...

Thank you very much in advance!

Ben

 

[texasalynn]

try this

Excel 2003
	A	B	C
1	A	1,2,3,4,5,6,7,8,9,10,11,12	1
2	B	2,3,4	0
3	C	1,6,12	0
4	A	2,6,7	0
5	A	1,2,3	1
6	C	8,9,5	0
7	B	10,2,5,6	0
8	A	1,2,3	1
9	C	3,4,5	0
10	A	6,7,7	0
Sheet2

Cell Formulas
Range	Formula
C1	=IF(AND(A1="A",ISNUMBER(FIND("|",SUBSTITUTE(B1,"1,","|")))),1,0)

copy the formula down in column c

 

[Benlafleche]

try this
Excel 2003

	A	B	C
1	A	1,2,3,4,5,6,7,8,9,10,11,12	1
2	B	2,3,4	0
3	C	1,6,12	0
4	A	2,6,7	0
5	A	1,2,3	1
6	C	8,9,5	0
7	B	10,2,5,6	0
8	A	1,2,3	1
9	C	3,4,5	0
10	A	6,7,7	0

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C1 =IF(AND(A1="A",ISNUMBER(FIND("|",SUBSTITUTE(B1,"1,","|")))),1,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

copy the formula down in column c

Thanks for this! However, that's not exactly what I am looking for. My need is quite specific. I would like to have a formula that counts directly the number of "1" in the 2nd column for each row with the value "A" in the first column.

Your solution pushes me to create a third column but I can't modify this sheet...

Any other idea?

Thanks again,

Ben

 

[par60056]

try this

Worksheet Formulas Cell Formula C1 =IF(AND(A1="A",ISNUMBER(FIND("|",SUBSTITUTE(B1,"1,","|")))),1,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

That doesn't work because it will find the 1s in 10, 11 or 12

 

[par60056]

try this slight modification.

=IF(AND(A1="A",ISNUMBER(FIND("|",SUBSTITUTE("," & B1 & ",",",1,","|")))),1,0) <tbody> </tbody>

<tbody>
</tbody>

This wraps the numbers between 2 commas. So for the substitute "1" becomes ",1," "1,2,3,4,5" is ",1,2,3,4,5," "0,2,3,4,10,11,12" is ",0,2,3,4,10,11,12," The first 2 will respond that there is a 1 and the last will not.

 

[texasalynn]

try this slight modification.

=IF(AND(A1="A",ISNUMBER(FIND("|",SUBSTITUTE("," & B1 & ",",",1,","|")))),1,0) <TBODY> </TBODY>

<TBODY>
</TBODY>

This wraps the numbers between 2 commas. So for the substitute "1" becomes ",1," "1,2,3,4,5" is ",1,2,3,4,5," "0,2,3,4,10,11,12" is ",0,2,3,4,10,11,12," The first 2 will respond that there is a 1 and the last will not.

par60056 - the OP doesn't want a helper column, so our suggestions doesn't work. But nice fix

 

[Marcelo Branco]

Maybe

=SUMPRODUCT(--($A$1:$A$10="A"),--(ISNUMBER(SEARCH(","&1&",",","&SUBSTITUTE($B$1:$B$10," ","")&","))))

M.

 

[Benlafleche]

Maybe

=SUMPRODUCT(--($A$1:$A$10="A"),--(ISNUMBER(SEARCH(","&1&",",","&SUBSTITUTE($B$1:$B$10," ","")&","))))

M.

Mr Marcelo Branco, Thank you!

This is EXACTLY what I was looking for!

I was trying without getting my way.. you did it. Thanks again. You deserve a gold medal!

Thanks also to the others who tried..

Ben

 

[Marcelo Branco]

Mr Marcelo Branco, Thank you!

This is EXACTLY what I was looking for!

I was trying without getting my way.. you did it. Thanks again. You deserve a gold medal!

Thanks also to the others who tried..

Ben

Ben,

You are very welcome and thanks for your kind words!

M.

 

[Robert Mika]

I thouhgt that the request was to count all 1 per cell?

Excel 2010

	A	B	C
1
2	A	1,2,3,4,5,6,7,8,9,10,11,12	8
3	B	2,3,4
4	C	1,6,12
5	A	2,6,7,1,1
6	A	1,2,3,1
7	C	8,9,5
8	B	10,2,5,6
9	A	1,2,3,1
10	C	3,4,5
11	A	6,7,7,1

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =SUMPRODUCT(--(A2:A11="A"),--(LEN(SUBSTITUTE(B2:B11,",",",,"))+2-LEN(SUBSTITUTE(","&SUBSTITUTE(B2:B11,",",",,")&",",",1,",",,")))) <tbody> </tbody>

<tbody>
</tbody>