Count distinct if lot of condition

[palo173]

Hi, please could you help me? It is difficult to explain, but I will try.

I want to count distinct of Tests in my Excel table.
For example for Test_1 I want to count all Tests, but only that kind of tests which have at least 1 Cond, which is in the same Cond as Test1. Result is 8 because condition is valid for Test_1, Test_2, Test_3, Test_4, Test_5, Test_6, Test_7, Test_10. I used Colours in table only for better idea of calculation for Test_1.

For Test_2 it is 7 (Test1, Test2, Test3, Test4, Test5, Test6, Test10).
For Test_10 it is only 5 (Test_1, Test_2, Test_3, Test_4, Test_10).

Column B is result, which I counted manually. I need some kind of formula in excel, because I have Lot of tests and Conds in table.

If it is not clear, I can add it.
Thank you

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUM(SIGN(MMULT(--$D$2:$M$11,TOCOL(--D2:M2))))

 

[palo173]

Hi & welcome to MrExcel.
How about

=SUM(SIGN(MMULT(--$D$2:$M$11,TOCOL(--D2:M2))))

It looks like good. Thank you so much. You are genius.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[palo173]

You're welcome & thanks for the feedback.

@Fluff , could I ask you for your aditional help with upgrade Excel formula? I try to counts only "aliquot counts".

So for first row (Test_1) it was formula =SUM(SIGN(MMULT(--$D$2:$M$11,TOCOL(--D2:M2)))) and result 1+1+1+1+1+1+1+0+0+1 = 8
But when I want to counts only aliquot part, result will be 1+0,8+0,6+0,6+0,8+0,6+0,2+0+0+0,2 = 4,8 (each number 1 from previous formula divided by 5, because Test_1 has 5 Conds).

I believe, it will helpful to somebody.
Thank you for your help

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		4.80		1	1	1	1	1
3		5.00		1	1	1	1
4		4.25		1	1	1						1
5		5.00			1	1	1
6		4.60		1		1	1	1	1
7		4.50		1			1	1	1
8		3.67						1	1	1
9		3.00							1	1	1
10		2.50								1	1
11		2.67			1							1	1
Data

Cell Formulas
Range	Formula
B2:B11	B2	=SUM(MMULT(--$D$2:$M$11,TOCOL(--D2:M2))/COUNT(D2:M2))