How do I count trio in excel?

With this table in cells A1:O20...

02 03 05 06 09 10 11 13 14 16 18 20 23 24 25
01 04 05 06 07 09 11 12 13 15 16 19 20 23 24
01 04 06 07 08 09 10 11 12 14 16 17 20 23 24
01 02 04 05 08 10 12 13 16 17 18 19 23 24 25
01 02 04 08 09 11 12 13 15 16 19 20 23 24 25
01 02 04 05 06 07 10 12 15 16 17 19 21 23 25
01 04 07 08 10 12 14 15 16 18 19 21 22 23 25
01 05 06 08 09 10 13 15 16 17 18 19 20 22 25
03 04 05 09 10 11 13 15 16 17 19 20 21 24 25
02 03 04 05 06 08 09 10 11 12 14 19 20 23 24
02 06 07 08 09 10 11 12 16 19 20 22 23 24 25
01 02 04 05 07 08 09 10 11 12 14 16 17 24 25
03 05 06 07 08 09 10 11 13 14 15 16 17 19 23
01 02 05 06 07 09 13 14 15 18 19 20 21 23 25
01 02 04 06 08 10 12 15 16 18 19 21 23 24 25
02 05 06 07 08 10 12 13 15 17 19 21 23 24 25
01 02 03 05 06 07 09 13 14 16 17 18 19 20 21
02 06 07 08 10 11 14 15 17 18 19 20 22 23 24
02 05 06 07 08 10 11 13 14 15 16 17 20 23 24
03 04 06 07 08 09 10 14 16 17 18 19 20 23 24

And this table in cells R1:T5...
06 10 23
09 19 20
01 16 25
02 07 10
04 09 12

This regular formula counts the number of times the referenced trio occurs within a row:

Please Login or Register  to view this content.

Copy that formula down through U5.

These will be the results in U1:U5
11
9
7
6
5

Is that something you can work with?

Mr. Ron

Is that something you can work with?
Answer: more or less, II intend to use in a set called Lotofacil games in Brazil, I would like to understand the formula to be able to change to 7 combinations, because I have an interesting program with some filters but I'm having problems with this formula as you can see ...

When I wrote the formula appeared in the error underlined item.
=SUMPRODUCT(--(FREQUENCY(--((($A$1:$O$20=R1)*ROW($A$1:$A$20))+(($A$1:$O$20=S1)*ROW($A$1:$A$20))+(($A$1:$O$20=T1)*ROW($A$1:$A$20))),ROW($A$1:$A$21)-1)=3))

Can you explain me what happens here?

Rafael

To best describe or illustrate your problem you would be better off attaching a dummy workbook instead of images, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Another approach, using VBA:

Please Login or Register  to view this content.

In x1...x5 the frequency of the respective combinations.

In x7 an example how to use this method with a combination of 7 numbers

Originally Posted by rafa.jsilva

Mr. Ron
When I wrote the formula appeared in the error underlined item.
=SUMPRODUCT(--(FREQUENCY(--((($A$1:$O$20=R1)*ROW($A$1:$A$20))+(($A$1:$O$20=S1)*ROW($A$1:$A$20))+(($A$1:$O$20=T1)*ROW($A$1:$A$20))),ROW($A$1:$A$21)-1)=3))

Can you explain me what happens here?

Rafael

The problem in the workbook you attached is that you changed the comma before the last ROW function into a plus-sign:
This: +ROW($A$1:$A$21)-1)=3))
Should be this: ,ROW($A$1:$A$21)-1)=3))

I attached an edited version of your workbook with working formulas. If it displays as an error in your PC, perhaps the problem occurs because of regional settings differences. Your formula separator may be a semi-colon, instead of a comma.

Try the attached, edited version of your file.

This is one of the formulas it uses:

Please Login or Register  to view this content.

Note: I replaced the spaces in the sheet names with underscores "_" to reduce punctuation and improve formula readability.

I hope that helps.