Count 1's by especial conditions

[motilulla]

Hello,

I need to count 1's "count 1 as one" if the find under same header in various columns

In the example given below there are 3 sets of 6 columns (with the same headers) I want to counts 1's (but if the are under same header count must be "as 1 count"

For example... in the 2nd set under n1 in the cell L7=1 & and in the 3rd set under n1 in the cell S7=1 (so far this will be count as 1) count of the 1's row7 = 3 result in the column Z.... and the same way is count of rest 1's shown for only 5 rows

Example data...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2
3
4
5
6				n1	n2	n3	n4	n5	n6			n1	n2	n3	n4	n5	n6		n1	n2	n3	n4	n5	n6		Sum
7				0	0	0	0	0	0			1	1	0	0	0	0		1	0	0	0	1	0		3
8				1	0	0	1	1	0			1	1	1	1	1	0		0	1	0	1	0	0		6
9				1	0	1	0	0	0			0	0	1	0	0	1		1	0	0	0	0	0		3
10				0	0	0	0	1	1			0	1	0	0	0	0		0	1	0	1	0	0		4
11				0	1	1	0	0	0			1	0	0	0	1	1		0	0	1	0	1	0		5
12				0	0	1	1	0	0			1	0	0	0	0	1		0	0	0	0	1	0		?
13				0	0	0	1	0	0			1	1	0	1	1	0		0	1	1	0	1	1		?
14				0	0	0	0	0	0			0	0	0	0	1	0		0	1	0	0	1	0		?
15				0	1	0	0	1	0			1	1	0	0	0	0		0	0	0	1	0	1		?
16				0	0	0	0	0	0			0	0	0	0	0	1		1	0	1	0	0	0		?
17				1	0	0	0	0	1			0	0	0	0	1	0		0	0	1	1	0	0		?
18				1	0	0	1	0	0			0	0	0	0	1	1		0	0	1	0	0	1		?
19				0	0	0	1	0	0			1	1	1	0	0	0		1	1	0	0	1	1		?
20				0	0	0	1	0	0			0	1	0	0	1	0		1	0	1	0	0	0		?
21				0	0	1	0	0	0			1	0	0	1	1	0		1	1	1	1	0	0		?
22				1	0	1	1	0	0			1	1	0	0	1	0		0	0	0	0	0	0		?
23				0	0	1	1	0	0			0	0	0	0	1	1		0	0	0	0	1	1		?
24				0	0	0	0	0	0			0	1	1	0	1	0		0	0	0	1	1	1		?
25				1	1	0	1	0	0			0	0	0	0	0	1		0	0	1	0	0	1		?
26				0	1	1	0	0	0			0	0	0	1	0	0		1	0	1	1	0	1		?
27				0	0	1	1	0	0			0	0	0	1	0	1		0	1	0	0	0	0		?
28				0	1	1	1	0	0			0	1	0	0	0	1		0	1	0	1	0	0		?
29				1	1	0	1	0	1			0	0	0	1	0	0		0	1	1	0	0	0		?
30				0	0	0	1	0	1			1	1	1	0	0	0		1	1	0	1	1	0		?
31				0	0	1	1	1	1			1	0	0	0	1	0		1	1	0	0	1	0		?
32				0	0	1	0	1	0			0	1	1	0	0	0		0	0	0	0	1	0		?
33				0	0	1	0	0	0			0	0	0	1	0	0		0	1	1	1	1	0		?
34				0	1	1	1	1	0			0	1	0	0	0	1		1	1	0	0	0	1		?
35				1	0	0	0	0	0			0	0	1	1	1	0		1	0	1	1	1	0		?
36				1	0	0	1	0	0			1	1	0	0	1	1		0	0	0	0	0	0		?
37				0	0	0	0	0	0			0	1	1	1	0	1		0	0	1	1	0	1		?
Sheet1

Thank you all

Excel 2000
Regards,
Moti

 

[Tetra201]

See if the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for you:

=SUM(SIGN(D7:I7+L7:Q7+S7:X7))

 

[motilulla]

See if the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for you:

=SUM(SIGN(D7:I7+L7:Q7+S7:X7))

Tetra201, Your formula worked just fantastic!</SPAN></SPAN>

Thank you so much for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[Tetra201]

You are most welcome.