Count how many times in total two values appear together in a row for all the rows

[Dainer]

Hello

The title may be confusing to understand but here is an example of what I need to achieve.

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

I would like to know a formula, that counts the rows where number "5" appears together with number "6" in the entire table and outputs how many of those rows are in total.

On the example table we can see that "5" and "6" appear together in 4 rows, so output = 4

Thank you in advance

 

[Tetra201]

If "5 and 6 appear together" means "5 and 6 in this very order in adjacent cells in the same row," then try the following array formula:

=SUM(--(MMULT((A1:E5=5)*(B1:F5=6),{1;1;1;1;1})>0))

If "5 and 6 appear together" means "5 and 6 anywhere in the same row," then try the following array formula:

=SUM((MMULT(--(A1:E5=5),{1;1;1;1;1})>0)*(MMULT(--(A1:E5=6),{1;1;1;1;1})>0))

Note: these array formulas should be entered using Ctrl+Shift+Enter, not just Enter.