Count Diagonals

[motilulla]

Hi everyone,</SPAN></SPAN>

The below example I have all 1 I want to count 2 Diagonal and 3 Diagonal as shown</SPAN></SPAN>

Where I need to count diagonal right side of the upper rows and left side of the lower rows</SPAN></SPAN>

For example1: if we check in row 5 we have 3 diagonal with 2 match</SPAN> (first in cell E5=1 and cell F4=1) second in cell J5=1 and cell K4=1, and last (third in cell L5=1 and M4=1) </SPAN></SPAN>

For example1: if we check in row 6 we have </SPAN>3 diagonal with 2 match and 1 with 3 match</SPAN> (first in cell E7=1 and cell E6=1) second in cell F7=1 And cell G6=1, and 3 diagonal match are In cell F7=1, cell G6=1 and Cell E8=1</SPAN></SPAN>

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Game	1	2	3	4	5	6	7	8	9	10	11	12	13	14	Total match 2 diagonal	Total match 3 diagonal
2	1		1
3	2			1						1					1
4	3					1		1			1		1
5	4	1			1				1	1		1		1		3
6	5				1		1								1
7	6	1		1		1										2	1
8	7				1							1			1	1
9	8	1				1	1	1		1	1					1
10	9				1				1							2
11	10
12	11	1	1		1	1		1	1		1
13	12											1		1
14	13				1		1				1		1			2
15	14		1

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Mr.Excel​

Results are shown in columns P And Q</SPAN></SPAN>

Please help</SPAN></SPAN>

Thanks And Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[shawnhet]

Kind of clunky but this should work, I believe.

in R4 put =B3+C2 and fill across to AD4 and down for however long of a range you need
in AE5 put =B4+C3+D2 and fill across to AP5 and down for your whole range.

Then in P2 put =COUNTIF(R3:AD3,2)
and in Q2 put =COUNTIF(AE3:AP3,3)
and fill down for your whole range.

Cheers,

 

[ZAX]

Press Alt+F11 to open the VBA window then from the Insert menu choose Module, Copy the following code and paste it in the white space in front of you.
In P2 type:
=CountDiagonals2($B2:$O2)
and copy down and in Q2 type:
=CountDiagonals3($B2:$O2)
and copy down.

Function CountDiagonals2(Range As Range)
If Range Is Nothing Or Range.Rows.Count > 1 Then Exit Function
For Each Cell In Range
    If Cell.Value = 1 And Cell.Offset(-1, 1).Value = 1 And Cell.Interior.Color = Cell.Offset(-1, 1).Interior.Color Then
       i = i + 1
    End If
Next
CountDiagonals2 = i
End Function

Function CountDiagonals3(Range As Range)
If Range Is Nothing Or Range.Rows.Count > 1 Then Exit Function
For Each Cell In Range
    If Cell.Value = 1 And Cell.Offset(-1, 1).Value = 1 And Cell.Offset(1, -1).Value = 1 And Cell.Interior.Color = Cell.Offset(-1, 1).Interior.Color And Cell.Interior.Color = Cell.Offset(1, -1).Interior.Color Then
       i = i + 1
    ElseIf Cell.Value = 1 And Cell.Offset(-1, 1).Value = 1 And Cell.Offset(-2, 2).Value = 1 And Cell.Interior.Color = Cell.Offset(-1, 1).Interior.Color And Cell.Interior.Color = Cell.Offset(-2, 2).Interior.Color Then
       i = i + 1
    End If
Next
CountDiagonals3 = i
End Function

Hope you understand and I'm sure this will help you!
ZAX

 

[Jerry Sullivan]

Another approach using Sumproduct....

In P3, and copy down
=IF(ROWS(P$2:P3)<2,0,SUMPRODUCT(--(B3:M3=1),--(C2:N2=1))-Q3)

In Q3, and copy down
=IF(ROWS(Q$2:Q3)<3,0,SUMPRODUCT(--(B3:M3=1),--(C2:N2=1),--(D1:O1=1)))

 

[ZAX]

Noticed that you want to count it in the 2 diagonal too!

DISREGARD...

 

[ZAX]

Another approach using Sumproduct....

In P3, and copy down
=IF(ROWS(P$2:P3)<2,0,SUMPRODUCT(--(B3:M3=1),--(C2:N2=1))-Q3)

In Q3, and copy down
=IF(ROWS(Q$2:Q3)<3,0,SUMPRODUCT(--(B3:M3=1),--(C2:N2=1),--(D1:O1=1)))

I could do it like this but wrote a macro for to include the color condition....

 

[motilulla]

Shawnhet, Thank you for your efforts it works Ok but......as you say….it is kind of clunky.</SPAN></SPAN>

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

 

[motilulla]

JS411, thank you for giving formula solution it is working as required 100% OK</SPAN></SPAN>

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

 

[motilulla]

ZAX, thank you for the function it giving result perfect. but in column Total match 3 diagonal is giving error #¡VALOR! as shown, what I am doing wrong so it is showing in some cells #¡VALOR!</SPAN></SPAN>

Please correct me.</SPAN></SPAN>

	P	Q
1	Total match 2 diagonal	Total match 3 diagonal
2	0	#¡VALOR!
3	0	0
4	0	#¡VALOR!
5	3	0
6	0	#¡VALOR!
7	2	1
8	1	#¡VALOR!
9	1	0
10	2	#¡VALOR!
11	0	0
12	0	#¡VALOR!
13	0	0
14	2	#¡VALOR!
15	0	0

<COLGROUP><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Diagonal Function​

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

 

[motilulla]

And one question more will it be possible to count diagonal if it is to Left or right side separately.</SPAN></SPAN>

For Example:</SPAN></SPAN>
Right side:</SPAN></SPAN>

	A	B	C	D	E	F	G	H	I	J
1	Game	1	2	3	4	5	6	7	Total match 2 diagonal	Total match 3 diagonal
2	1		1				1
3	2	1			1			1	1
4	3			1					1
5	4		1						1	1
6	5

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Right-Diagonal​

Left side:</SPAN></SPAN>

	A	B	C	D	E	F	G	H	I	J
1	Game	1	2	3	4	5	6	7	Total match 2 diagonal	Total match 3 diagonal
2	1	1					1
3	2		1			1			1
4	3			1					1	1
5	4
6	5

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Left-Diagonal​

Thanks And Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>