Determine if a date is prior to another date/year - include as criterion

Lorna Anderson

I would like to determine if a date is prior to 01/01/2015 and include it as criterion for the count/distinct/collect combination I inquired about previously??

=COUNT(DISTINCT(COLLECT({CIP Detail Summary Range 4}, {CIP Detail Summary Range 5}, ="OPEN", {CIP Detail Summary Range 3}, =0, IF(YEAR({CIP Detail Summary Range 1} < 2015)))))

Paul Newcome

Because of the range, criteria, range, criteria, etc syntax, you would need to specify the range first then use an @cell reference in the criteria.

=COUNT(DISTINCT(COLLECT({CIP Detail Summary Range 4}, {CIP Detail Summary Range 5}, ="OPEN", {CIP Detail Summary Range 3}, =0, {CIP Detail Summary Range 1}, IFERROR(YEAR(@cell), 2016) < 2015)))