COUNTIF for distinct values only for two conditions

It is not clear which values you are wishing to count and the criteria

your sumproduct is just
=COUNTIFS(B2:B9,"*-*") each cell has a - in with some characters before and after, so it returns 8


what is the 6 made up of? you have not been explicit, It may be 10-1 , 5-1, 5-2, 5-5, 5-3, 5-4 the unique values with a - in the middle. But you have not said, putting multiple values in 1 cell which you have done, makes calculation far harder.

Sorry. I'll explain in a second, hold tight

Here is the updated excel with the additional criteria.

So you suggest i "unmerge" the cells then, instead of ALT-ENTER use enter instead into a new row? Hmm ok. If you're sure it can't be solved this way

Rationale was less rows in the spreadsheet, but i guess i'm "adding" rows "space" by doing ALT Enter anyway.
I've added colours and disbanded them into separate rows to make it easier to identify which is which pairing.

Hi

Try this huge formula

Formula:

Please Login or Register  to view this content.

Thanks for this, it works on the sheet, i'll check if it works on the data on Monday! cheers bud!

And thanks ~ALiG will do, i'll organise it better on Monday

Ok so just to come back to this query again, as it is now Monday Morning!

The formula:

Formula:

Please Login or Register  to view this content.

works on the sample data sheet but not on all of the data in the real sheet.

So to reduce any further errors in the future, I have attached the same workbook but with all of the data from the parts this time. With part numbers and process numbers for clarity.

Cheers,
Julian

To be clear, the end of the data in Cell P is where I want the formula to be. I used the formula that was mentioned by Jose in post#15 but it does not work for some reason with the full data.
I really have no idea what the formula is doing, but from mini research i know that 1/COUNTIF(Cell range, Cell range) find duplicates so that's good I suppose?

What I want is highlighted in yellow in R171

Out of interest could it be possible to use this formula but ignore blanks?

Formula:

Please Login or Register  to view this content.

OR When I sort my data the rows get hidden anyway, could this formula be used avoiding hidden rows?

Formula:

Please Login or Register  to view this content.

I had a bit of tinkering around this morning with the formula and tried the COUNTBLANK function.
the formula became:

Formula:

Please Login or Register  to view this content.

This, however, does not work and still gives DIV#/0

Perhaps the below as a helper column
=IFERROR(1/COUNTIFS($P$2:$P$170,P2,$P$2:$P$170,"*?-?*")*CEILING(COUNTIFS($P$2:$P$170,P2,$P$2:$P$170,"*?-?*")/2,1),0)


seems to work on a small test

That seems to work! Awesome!
Out of any interest is there a way to do it without a helper column? No worries if not! That works just fine!

You could have a custom function written in vba to return the answer.
below is a rough guide to them, but I am not expert using them!

https://www.excel-easy.com/vba/examp...-function.html


As a formula it starts to get messy. You having one instance where there is not a duplicate, makes it harder to do, as does having blanks in the data. There may be a way, but I've not thought of it yet!

You having one instance where there is not a duplicate, makes it harder to do, as does having blanks in the data.

Ah OK, so if I were to eliminate the one where it says 2-2 (with itself) or double it and say 2-2, then that would possibly be able to avoid helper column?

Like this?

if you avoided the single entries (or doubled them!)

you can then count the pattern and divide by 2
COUNTIFS($P$2:$P$170,"*?-?*")/2

is your total

Perfect! You've been very helpful davsth! Cheers,
PROBLEM SOLVED