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

D

DaScientist

New Member
Joined
May 17, 2018
Messages
5
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Are you happy using a UDF? Simply enter =Triad(A1:A9)
Code: 
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
 
Last edited:
Upvote 0
Welcome to the forum.

Here's an array formula that should work:

Code: 
=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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Another way:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td]
1​
[/td][td="bgcolor:#E5E5E5"]
4​
[/td][td]B1: =SUMPRODUCT(--(--(A1:A7 & A2:A8 & A3:A9) = {123,132,213,231,312,321}))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]
2​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]
3​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]
2​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]
3​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]
1​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]
2​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td]
1​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td]
3​
[/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
One final idea. If you're willing to use a helper column, this works:

ABCDEFG
HelperFormula3Formula1Formula2shg's Formula
9

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=SUMPRODUCT(--(--(A2:A8 & A3:A9 & A4:A10) = {123,132,213,231,312,321}))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(PRODUCT(COUNTIF(A2:A4,{1,2,3}))*SUM(A2:A4)=6,1,"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=SUM(B:B)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




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.
 
Upvote 0
Eric W said:
One final idea. If you're willing to use a helper column, this works:

ABCDEFG
HelperFormula3Formula1Formula2shg's Formula
9

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet8
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=SUMPRODUCT(--(--(A2:A8 & A3:A9 & A4:A10) = {123,132,213,231,312,321}))
[/TD]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=IF(PRODUCT(COUNTIF(A2:A4,{1,2,3}))*SUM(A2:A4)=6,1,"")[/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=SUM(B:B)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




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.
Click to expand...



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.
 
Upvote 0
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.
 
Upvote 0
njimack said:
Are you happy using a UDF? Simply enter =Triad(A1:A9)
Code: 
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
Click to expand...

njimack your code works like a charm, I don't know what happened before and it did not work. Thank you so much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Need help! extract value in special case, find many method, cannot solve it!
Replies
6
Views
177
CLKinfo
C
D
  • Solved
Formula for column for (sort of) running count
Replies
7
Views
124
Peter_SSs
Peter_SSs
K
  • Solved
Count with criteria
2
Replies
19
Views
277
Peter_SSs
Peter_SSs
U
  • Solved
Count Number of Times Values appear in a Column and Rank top 3
Replies
5
Views
203
Fluff
Fluff
M
  • Question Question
How to "operate" with a cell in a previous worksheet?
Replies
3
Views
77
abdelfattah
A

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top