CODEWITHSUNDEEP
excel
chrisheinze
chrisheinze

Reputation: 1189

Counting unique values within date range

I'm looking to count the number of distinct values (machineId's) that fall between a date range.

Start date: B2

End date: C2

machineId: 'Production Data'!E:E

allDates: 'Production Data'!AH:AH

The formula I have right now is =SUM(--(FREQUENCY(IF('Production Data'!AH:AH>=B3, IF('Production Data'!AH:AH<C3, 'Production Data'!E:E)), 'Production Data'!E:E)))

which only returns #VALUE!

Thanks.

Upvotes: 0

Views: 2508

Answers (1)

Fabricator
Fabricator

Reputation: 12782

Use the following array formula:

={SUM(IF(FREQUENCY(IF(($B$2<=AH:AH)*(AH:AH<=$C$2),E:E,""),
                   IF(($B$2<=AH:AH)*(AH:AH<=$C$2),E:E,""))>0,1))}

reference

Upvotes: 1

Related Questions