I am trying to create a formula that will return the numbers of resource that has possible duplicate-CodePudding

my goal is to count the number of person that having different criteria to consider.

the below formula is working fine but not sure how are we going to put another criteria

=SUMPRODUCT((A5:A10000<>"")/COUNTIFS(A5:A10000,A5:A10000&""))

that formula will count duplicates as 1 but i also want to consider the date from other column

somehow like this if we are not going to consider the duplicates =countifs(startdatecolumn, 1/1/2022,enddatecolumn, 5/3/2022)

i tried to include that here COUNTIFS(A5:A10000,A5:A10000&"") like =SUMPRODUCT((A5:A10000<>"")/COUNTIFS(A5:A10000,A5:A10000&"",startdatecolumn, 1/1/2022,enddatecolumn, 5/3/2022) but its not working

CodePudding user response：

The reciprocal SUMPRODUCT/COUNTIFS is incredibly inefficient. Much better is:

=SUM(IF(FREQUENCY(IF(A5:A10000<>"",IF(B5:B10000=DATEVALUE("1/1/2022"),IF(C5:C10000=DATEVALUE("5/3/2022"),MATCH(A5:A10000,A5:A10000,0)))),ROW(A5:A10000)-MIN(ROW(A5:A10000)) 1),1))

where I've assumed that B5:B10000 and C5:C10000 contain yout start and end dates respectively.

Obviously there are even more efficient set-ups if you have O365.