Count duplicates if other cell contains week num google sheets

Question

I am fighting to get a duplicate count to work.

A	B	C	D
3/20/2024	Data A	Paul Jones	12
3/19/2024	Data B	Paul Simons	12
3/19/2024	Data B	Paul Simons	12
3/16/2024	Data C	Bob More	11
3/8/2024	Data A	Jack Silvan	10
3/7/2024	Data A	Jack Silvan	10
3/6/2024	Data D	Marc Stone	10
3/5/2024	Data D	Marc Stone	10

I have the following list of weeks on column E

I want column F to count the amount of duplicates in col C when Col D week is the same as the list in column E. I use =ArrayFormula(if(A2:A="",,ISOWEEKNUM(A2:A))) in column D I tried =IF(D2:D=E2, INDEX(ARRAYFORMULA(IF(LEN(C2:C),SUM(N(COUNTIFS(C2:C, C2:C, ROW(C2:C), "<="&ROW(C2:C))>1)),)), 1, 1), "")

I cannot get it to work. Here is what I am looking for:

I hope it makes sense

Answer 1

Here's one approach you may test out:

=map(sequence(50),lambda(Σ,hstack(Σ,index(countif(isoweeknum(A:A),Σ)-countunique(ifna(filter(A:A&C:C,isoweeknum(A:A)=Σ)))))))

• this does not require helper columns

Answer 2

UPDATE: If you want to know the no. of duplicates per week for this particular problem, we can do the following:

If we count the no. of entries per date, name and week, it would look like this:

As you can see, we are able to count the no. of entries of a name for a given date and week.

If we want the no. of duplicates, we just deduct 1 from the count, which represents the original copy.

We only need the week no.s, and therefore, we'll only show it together with the no. of duplicates.

This will lead to the first part of the formula:

Next, we can use the above result to aggregate the no. of weeks and sum the counts to return the total no. of duplicates per week:

Finally, we can now use this final result to lookup the week no. and return the no. of duplicates.

Here's the final formula:

=ArrayFormula(LET(countDupPerDate,QUERY($A$2:$D$9,"SELECT D, COUNT(A)-1 GROUP BY A,C,D LABEL COUNT(A)-1 ''",0),
                  countDupPerWeek,QUERY(countDupPerDate,"SELECT Col1,SUM(Col2) GROUP BY Col1 LABEL SUM(Col2) ''",0),
                  searchDup,IFNA(VLOOKUP(E2:E4,countDupPerWeek,2,FALSE),0),
                  searchDup))

Note: Just change the ranges $A$2:$D$9 and E2:E4 to their appropriate ranges.