Count matches from cells

**Gulya** · 11-19-2022, 03:09 PM

Hi Experts,

I would like to count number of matches within another last 3 cells.
For instance, A32=186927 so have to count 1,8,6,9,2,7 from last 3 cells (A31,A30 and A29). and if any matched number is >0 then have to consider as a 1.
I have attached sample file for easy reference please.
Thanks

**HansDouwe** · 11-19-2022, 04:11 PM

Please add some expected results

**Gulya** · 11-19-2022, 04:31 PM

I have added the attachment with outcome.
the results in column B:E should come from column A and column G:L is just a sample to show the expected result.

**HansDouwe** · 11-19-2022, 05:39 PM

Please try for I3 in F7 and copy down:

Formula:

Please Login or Register  to view this content.

and for I5 in G7 and copy down:

Formula:

Please Login or Register  to view this content.

and for MAX3 in I7 and copy down:

Formula:

Please Login or Register  to view this content.

and for MAX5 in J7 and copy down:

Formula:

Please Login or Register  to view this content.

**Gulya** · 11-19-2022, 06:12 PM

as expected working perfectly, thanks a lot Hans.
But I forgot to mention one criteria,
For example A36=213215 so can we avoid duplicates and consider like 2135.
Something like below;
=SUM(SIGN(IFERROR(FIND(MID(A36=2135,SEQUENCE(LEN(A36=2135)),1),CONCAT(A33:A35)),0)))

**HansDouwe** · 11-19-2022, 06:35 PM

You didn't even take that into account in the example. This is easily solved for I3 and I5 by adding UNIQUE to the formula.

The I3 formula then becomes:

Formula:

Please Login or Register  to view this content.

Does this also apply to the MAX formula?
If so, what exactly is the rule and expected outcome?

**Gulya** · 11-19-2022, 06:41 PM

Thanks a lot Hans, working perfectly.
No, it's not applicable to Max.

**HansDouwe** · 11-19-2022, 07:12 PM

Thanks for the feedback and glad to help.
Please considder adding reputation to the answer(s) if you think the answer(s) deserve it.

**Gulya** · 11-20-2022, 03:27 AM

Usually I always add reputation, but this time am not able as the highlighted message in the attachment is appearing.
I will definitely add whenever there is enough reputation to add
Once again thanks Hans

**HansDouwe** · 11-20-2022, 03:54 AM

Oh yes, that's from the previous tread about the gaps between the numbers.
You may not give reputation to the same person twice in a row.

Thank you very much anyway

Count matches from cells

LinkBack

Thread Tools

Rate This Thread

Display

Count matches from cells

Re: Count matches from cells

Re: Count matches from cells

Re: Count matches from cells

Re: Count matches from cells

Re: Count matches from cells

Re: Count matches from cells

Re: Count matches from cells

Re: Count matches from cells

Re: Count matches from cells

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count Cells that matches text and colorindex

[SOLVED] Count Ifs First 10 matches

[SOLVED] Count number of occurrences but if there are consecutive matches count them as one

Count row if any of a range of cells matches text

Count.If if value in other cell in the row matches

[SOLVED] how do I count pairs of cells when each matches a condition?

COUNT INFORMATION IN COLUMN B ONLY IF A1 MATCHES K1 COUNT THAT CE.

Bookmarks

Bookmarks

Posting Permissions