Counting the Duplicates from Google Sheets

Question

I am having difficulty in finding the correct function of a combination of functions in Google Sheets.

I tried this one:

=ARRAYFORMULA(SUM(N(IF('//WORKLOG'!$B$5:$B=$A4,(COUNTIFS('//WORKLOG'!$J$5:$J, '//WORKLOG'!$J$5:$J, ROW('//WORKLOG'!$J$5:$J), "<="&ROW('//WORKLOG'!$J$5:$J))>1)))))

but it only gives me random results.

Here's an overview.

The Dataset

Name           State      Date          Order  Qty
April Smith IL  11/1/2023   Apple   3
Jane Doe    IL  11/3/2023   Orange  1
James Smith GA  11/9/2023   Apple   5
Jessica Roberts IL  11/10/2023  Apple   3
Mary Kaye   IL  11/17/2023  Orange  1
Christine May   GA  11/20/2023  Apple   5
April Smith IL  11/1/2023   Apple   3
Jane Doe    IL  11/3/2023   Orange  1
James Smith GA  11/9/2023   Apple   5
April Smith IL  11/10/2023  Apple   3

It should be like this in my summary

Name              Date    Count of Duplicates
April Smith 11/1/2023   1
Jane Doe    11/3/2023   1
James Smith 11/9/2023   1

Answer 1

Try something like-

=LET(x,INDEX(COUNTIFS(A2:A,A2:A,C2:C,C2:C)-1),UNIQUE(CHOOSECOLS(FILTER(HSTACK(A2:C,x),x>0),1,3,4)))