Formula for counting triads that contain 1, 2, 3 in columns.

[DaScientist]

Hello,

I'm trying to find a way to count triads of 1, 2, 3 in columns.
For example I have:
1
2
3
2
3
1
2
1
3

In this example I have 123, 231, 312, 213. So a total sum of 4 triads. A triad must have all 3 different numbers to count(1, 2, 3). So a triad of 323 doesn't count.
Is there any way to do that?
I would really appreciate your help. I have a lot of data and it will take ages to process them manually.

 

[njimack]

Are you happy using a UDF? Simply enter =Triad(A1:A9)

Function Triad(rng As Range)
Application.Volatile
Dim i As Long
Dim Triad_Check As Boolean
For i = 2 To rng.Rows.Count
Triad_Check = False
    Select Case rng(i)
        Case 1
            If rng(i - 1) = 2 And rng(i + 1) = 3 Then Triad_Check = True
            If rng(i - 1) = 3 And rng(i + 1) = 2 Then Triad_Check = True
            
        Case 2
            If rng(i - 1) = 1 And rng(i + 1) = 3 Then Triad_Check = True
            If rng(i - 1) = 3 And rng(i + 1) = 1 Then Triad_Check = True
        
        Case 3
            If rng(i - 1) = 1 And rng(i + 1) = 2 Then Triad_Check = True
            If rng(i - 1) = 2 And rng(i + 1) = 1 Then Triad_Check = True
    End Select
If Triad_Check = True Then Triad = Triad + 1
Next i
        
                
End Function

 

[Eric W]

Welcome to the forum.

Here's an array formula that should work:

=SUM(IFERROR(CHOOSE($A$2:$A$8,CHOOSE($A$3:$A$9,0,CHOOSE($A$4:$A$10,0,0,1),CHOOSE($A$4:$A$10,0,1,0)),CHOOSE($A$3:$A$9,CHOOSE($A$4:$A$10,0,0,1),0,CHOOSE($A$4:$A$10,1,0,0)),CHOOSE($A$3:$A$9,CHOOSE($A$4:$A$10,0,1,0),CHOOSE($A$4:$A$10,1,0,0),0)),0))

confirmed with Control+Shift+Enter.

It seems a bit awkward, but it seems to work. I feel there should be a better way, if I think of one, I'll let you know.

 

[DaScientist]

Hello njimack,
I really appreciate your help. I tried that, however an error occurs. Am I doing something wrong? Could you please verify that the code is ok?
Thank you.

 

[Eric W]

This formula is somewhat shorter:

=SUM(IFERROR(IF((MATCH($A$2:$A$8,{1,2,99,3},0)+MATCH($A$3:$A$9,{1,2,99,3},0)+MATCH($A$4:$A$10,{1,2,99,3},0)=7)*(SUBTOTAL(9,OFFSET($A$2,ROW($A$2:$A$8)-ROW($A$2),0,3))=6),1),0))

also with Control+Shift+Enter. The 99 in the formula should just be some number that will never appear in your range.

 

[shg]

Another way:

	A​	B​	C​
1​	1​	4​	B1: =SUMPRODUCT(--(--(A1:A7 & A2:A8 & A3:A9) = {123,132,213,231,312,321}))
2​	2​
3​	3​
4​	2​
5​	3​
6​	1​
7​	2​
8​	1​
9​	3​

 

[Eric W]

One final idea. If you're willing to use a helper column, this works:

	A	B	C	D	E	F	G
1		Helper	Formula3		Formula1	Formula2	shg's Formula
2	1	1	4		4	4	4
3	2
4	3
5	2	1
6	3	1
7	1
8	2	1
9	1
10	3

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula G2 =SUMPRODUCT(--(--(A2:A8 & A3:A9 & A4:A10) = {123,132,213,231,312,321})) B2 =IF(PRODUCT(COUNTIF(A2:A4,{1,2,3}))*SUM(A2:A4)=6,1,"") C2 =SUM(B:B) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=SUM(IFERROR(CHOOSE($A$2:$A$8,CHOOSE($A$3:$A$9,0,CHOOSE($A$4:$A$10,0,0,1),CHO OSE($A$4:$A$10,0,1,0)),CHOOSE($A$3:$A$9,CHOOSE($A$4:$A$10,0,0,1),0,CHOOSE($A$4:$A$10,1,0,0)),CHOOSE($A$3:$A$9,CHOOSE($A$4:$A$10,0,1,0),CHOOSE($A$4:$A$10,1,0,0),0)),0))} F2 {=SUM(IFERROR(IF((MATCH($A$2:$A$8,{1,2,99,3},0)+MATCH($A$3:$A$9,{1,2,99,3},0)+MATCH($A$4:$A$10,{1,2,99,3},0)=7)*(SUBTOTAL(9,OFFSET($A$2,ROW($A$2:$A$8)-ROW($A$2),0,3))=6),1),0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the formula in B2 and drag down. Each triad is marked with a 1, and the sum of column B (the C2 formula) is the number of triads.

 

[DaScientist]

One final idea. If you're willing to use a helper column, this works:

	A	B	C	D	E	F	G
1		Helper	Formula3		Formula1	Formula2	shg's Formula
2	1	1	4		4	4	4
3	2
4	3
5	2	1
6	3	1
7	1
8	2	1
9	1
10	3

<tbody>
</tbody>

Sheet8
​

Worksheet Formulas Cell Formula G2 =SUMPRODUCT(--(--(A2:A8 & A3:A9 & A4:A10) = {123,132,213,231,312,321})) B2 =IF(PRODUCT(COUNTIF(A2:A4,{1,2,3}))*SUM(A2:A4)=6,1,"") C2 =SUM(B:B) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=SUM(IFERROR(CHOOSE($A$2:$A$8,CHOOSE($A$3:$A$9,0,CHOOSE($A$4:$A$10,0,0,1),CHO OSE($A$4:$A$10,0,1,0)),CHOOSE($A$3:$A$9,CHOOSE($A$4:$A$10,0,0,1),0,CHOOSE($A$4:$A$10,1,0,0)),CHOOSE($A$3:$A$9,CHOOSE($A$4:$A$10,0,1,0),CHOOSE($A$4:$A$10,1,0,0),0)),0))} F2 {=SUM(IFERROR(IF((MATCH($A$2:$A$8,{1,2,99,3},0)+MATCH($A$3:$A$9,{1,2,99,3},0)+MATCH($A$4:$A$10,{1,2,99,3},0)=7)*(SUBTOTAL(9,OFFSET($A$2,ROW($A$2:$A$8)-ROW($A$2),0,3))=6),1),0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the formula in B2 and drag down. Each triad is marked with a 1, and the sum of column B (the C2 formula) is the number of triads.

First of all, thank you all for your help.
I've tried this formula =SUMPRODUCT(--(--(A2:A8 & A3:A9 & A4:A10) = {123,132,213,231,312,321})) but excel tries to correct it into this:=SUMPRODUCT(--(--(A2:A8 & A3:A9 & A4:A10) = {123,132213231312321}))

Moreover, I tried copy-pasting or typing the B2, or C2 formulas, however excel gives an error.
I have basic excel knowledge, so I would like to eliminate the possibility that I am doing something wrong.

The Ctrl+Shift+Enter commands also do not work. Is there a chance to make a UDF code?
If you are positive that these formulas work, then I'll do some studying on my own to fix whatever I am doing wrong

Thanks for your patience.

 

[DaScientist]

If I put only this: =SUMPRODUCT(--(--(A2:A8 & A3:A9 & A4:A10) = {321})) , I get value 1 which is correct. However when I put more values(321,123,213) I get an error. Any idea what's happening? Seems simple and effective, if I overcome this.

 

[DaScientist]

Are you happy using a UDF? Simply enter =Triad(A1:A9)

Function Triad(rng As Range)
Application.Volatile
Dim i As Long
Dim Triad_Check As Boolean
For i = 2 To rng.Rows.Count
Triad_Check = False
    Select Case rng(i)
        Case 1
            If rng(i - 1) = 2 And rng(i + 1) = 3 Then Triad_Check = True
            If rng(i - 1) = 3 And rng(i + 1) = 2 Then Triad_Check = True
            
        Case 2
            If rng(i - 1) = 1 And rng(i + 1) = 3 Then Triad_Check = True
            If rng(i - 1) = 3 And rng(i + 1) = 1 Then Triad_Check = True
        
        Case 3
            If rng(i - 1) = 1 And rng(i + 1) = 2 Then Triad_Check = True
            If rng(i - 1) = 2 And rng(i + 1) = 1 Then Triad_Check = True
    End Select
If Triad_Check = True Then Triad = Triad + 1
Next i
        
                
End Function

njimack your code works like a charm, I don't know what happened before and it did not work. Thank you so much.