# If Frequency Formula count based on date is off by 1

1. ## If Frequency Formula count based on date is off by 1

In column L, I have an array that is counting the distinct numbers in column C for for the specific Month and Year in Column A. For some reason, for the months that don't start in row A2, in this case March and April, the count are off by 1. In summary, Feb 2015 count is spot on, but then March and April are both 1 too many.

Any ideas on what may be causing this?

2. ## Re: If Frequency Formula count based on date is off by 1

I think that you will be counting blanks as zero values in some circumstances, try this revised version

=SUM(IF(FREQUENCY(IF(MONTH(\$A\$2:\$A\$25000)=MONTH(A2),IF(YEAR(\$A\$2:\$A\$25000)=YEAR(A2),IF(\$C\$2:\$C\$25000<>"",\$C\$2:\$C\$25000))),\$C\$2:\$C\$25000)>0,1))

or you can shorten the MONTH/YEAR parts like this for the same results:

=SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$25000-DAY(\$A\$2:\$A\$25000)=A2-DAY(A2),IF(\$C\$2:\$C\$25000<>"",\$C\$2:\$C\$25000)),\$C\$2:\$C\$25000),1))

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1