"Exclude duplicate" in the countifs formula.

Hello everyone,

A suggestion to test by adding a month column to your data. The month is chosen in N1, the result is in N2.
https://www.cjoint.com/c/KDCmy0Y3DN1
If I understood correctly

Best regards

Hello
not sure I understood everything. See this model in which the items from the selected month are counted:

• one each time F is empty
• one only for each reference in the same month

still to know if that's really what you want? (in any case, February shows 4 correctly)
best regards
https://mon-partage.fr/f/I4vJd7JF/

Hello and thank you !!!

However, I don't understand everything about the behavior of these formulas.

I'm doing a column game with Yclik's solution (columns I and J) and a game with Vaucluse's solution (columns G and H).

For both, I paste in G2 =COUNTIF($F$2:F2;F2) and in I2 =COUNTIF($F$2:$F$13;F2)
I drag these formulas down. For the first, it compares F2 to the range F2:F2 on row 2 and in the end compares in row 13 the cell F13 in a table F2:F13.
In the second case, we directly compare the cell (F2 for row 2) to the table F2:F13.
In the end, I also compare F13 to a table F2:F130

Then, I paste in H2 =SUMPRODUCT((MONTH(B2:B13)=2)*(G2:G13=1)).
I also paste the formula in J3 =SUMPRODUCT((MONTH(B2:B13)=2)*(F2:F13<>"")*(I2:I13=1)) (modified with respect to its reference column "I" instead of "G")

In H
I get the correct result, in J3 I get 0 (that confirms what I feared about Vaucluse). BUT... I wanted to slightly modify the values in F2:F13, I added other duplicates, I removed others (as if the data were living a bit) and there it goes haywire! I added the value "65833" in February, the formula does not like it at all... for me there are 3 occurrences and the 2 formulas only return 2.

I'm giving you the file with what I did... did I miss a step?

https://www.cjoint.com/c/KDCkDSL7NSD

Thanks anyway, it seems to be on the right track!!!

Hello,

Great, the two work perfectly!
But there are 2 points I hadn't thought of at all when making my example table, and I've wasted your time by omitting this. I would completely understand if you decided to drop this subject.

First, this issue of identical numbers across different months: I didn't realize this was why it gave me an inconsistency. It's not inconsistent indeed! If the month changes, it's no longer a duplicate value.
So as you did, that's very good.

Next, and this is the most troubling: column 6 is not always full. My example did not indicate this at all.
Each row is counted, each row with a blank in column 6 counts. But if column 6 contains a number that already exists in the same month, the rows with that duplicate number should only count as one row.

Since I have difficulty seeing what is important, I will try to be exhaustive:

Column 1 is always full, necessarily a number, necessarily unique but does not have to be consecutive (it may be sorted in order if necessary). It's the row creation number, so its value has no limit.

Column 2 is necessarily a date in the already indicated format (dd/mm/yyyy hh:mm), necessarily filled but not necessarily unique.

Columns 3 and 4 are necessarily filled with the same criteria "5" and "S".

Column 5 is not necessarily filled and contains various alphanumeric characters of different lengths.

Column 6 can be empty or numeric (only this choice). Numbers can repeat. Erratic behavior: we can have x different successive numbers followed by a blank of x lines followed by x successive numbers of which x are already present higher up in the column (and/or will be present again lower down) etc.

I'm sorry for the time my ignorance has caused you to waste!

On this link, I've updated the example... the duplicate "65833" is just an example.

https://www.cjoint.com/c/KDEke1HbG8D

That's exactly it and it works on the original file.

A big thank you, and sorry again for the difficulty of the explanations...

THANK YOU all!