# SUMIF array formula to count unique values after a certain date

1. ## SUMIF array formula to count unique values after a certain date

Hi,

So I need to count the unique entries in column D but only after 1st August 2013.

The formula I have come up with is:

=SUM(IF(FREQUENCY(IF(Projects!\$U\$1:\$U\$1328 >=1/8/2013, IF(Projects!\$D\$1:\$D\$1328<>"",MATCH(Projects!\$D\$1:\$D\$1328,Projects!\$D\$1:\$D\$1328,0))),ROW(Projects!\$D\$1:\$D\$1328)-ROW(Projects!\$D\$1)+1),1))

But for some reason this is counting ALL the unique values in column D regardless of date. Can anyone tell me what I have done wrong?

Thanks!

Mish

2. ## Re: SUMIF array formula to count unique values after a certain date

Use a cell to hold the date then refer to that cell:

A1 = 1/8/2013

=SUM(IF(FREQUENCY(IF(Projects!\$U\$1:\$U\$1328>=A1,IF(Projects!\$D\$1:\$D\$1328<>"",MATCH(Projects!\$D\$1:\$D\$1328,Projects!\$D\$1:\$D\$1328,0))),ROW(Projects!\$D\$1:\$D\$1328)-ROW(Projects!\$D\$1)+1),1))

Still array entered!

The way you had it, Excel evaluated it as >= 1 divided by 8 divided by 2013 or 0.0000620963735717834.

3. ## Re: SUMIF array formula to count unique values after a certain date

Brilliant! Thanks!

4. ## Re: SUMIF array formula to count unique values after a certain date

You're welcome. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)