sumproduct with unique values

[doug firr]

I'm using a formula, learned on this forum, to count only unique records within a sumproduct formula.

=SUMPRODUCT(($B$18:$B$741>=C4)*($B$18:$B$741<=C5)*(1/COUNTIF($D$18:$D$741,$D$18:$D$741)))

C4 and C5 are dates - I wish to include records within two date ranges. The data in column D are people - registration IDs.

I'd like to know the unique number of people within a date range.

By using the auto filter I know that I need to arrive at 124 people. Right now I get 67.06 so I know there must be a problem since I know that the number must be an integer.

Can anyone spot any issues with the formula or make any suggestions of what to check? I use the formula frequently and it normally works great.

 

[Marcelo Branco]

Try this array formula

=SUM(IF(FREQUENCY(IF($B$18:$B$741>=C4,IF($B$18:$B$741<=C5,MATCH($D$18:$D$741,$D$18:$D$741,0))),LIN($D$18:$D$741)-LIN($D$18)+1),1))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

M.