"Exclude duplicate" in the countifs formula. Solved

Question

Hello,  In the attached anonymized file as an example, I would like to find out how to include in my initial formula "countifs" the notion of duplicates and exclude repeated occurrences. Or use a completely different formula if necessary!  https://www.cjoint.com/c/KDBkqUrRVPD  1st criterion It looks for dates in column 2 to return the number of occurrences per month.  2nd criterion It checks if column F is filled.  3rd criterion I would like to exclude possible duplicates in column F (for x identical occurrences in column F, count only 1), and I am stuck there.  In the original file, the values are in another tab, and this tab contains the "source" data, I would like to avoid modifying this tab...  Does anyone see a solution?  Thank you!!

yclik · Answer

Hello a suggestion in G2 =COUNTIF($F$2:$F$13,F2)   in H2 for February =SUMPRODUCT((MONTH(B2:B13)=2)*(F2:F13<>"")*(G2:G13=1))  but it is not stated whether the exclusion of duplicates is done for the concerned month or for the entire column.

stitchbouck · Answer

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!!!

ALS35 · Answer

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

stitchbouck · Answer

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

Vaucluse · Answer

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/

stitchbouck · Answer

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!

"Exclude duplicate" in the countifs formula.

6 answers

Site unreachable

Reinstallation of w11 impossible after pc crash...

Nonexistent sent items folder

Transfer mp3 from pc to mobile

Increase the capacity of free's zimbra webmail to 10gb

Unrefreshed usb drive on android interface

Tencent impossible to remove

Is there enough storage space on my vps?

Car gps tracker

Erreur de captcha