Search code examples
arraysgoogle-sheetsarray-formulas

Count duplicates if other cell contains week num google sheets

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 List of weeks

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:

enter image description here

I hope it makes sense

Solution

  • 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)=Σ)))))))

    enter image description here

    • this does not require helper columns