# Counting distinct values with blank cells produces #DIV/0!

1. ## Counting distinct values with blank cells produces #DIV/0!

I am trying to count distinct values of a column based on a date range. But since there are cells within that column the result produces a #DIV/0! Are there other ways to produce this?

Here is the formula I am using:

``Please Login or Register  to view this content.``

'Error Report Data'!\$A\$2:\$A\$25000 = DATES
\$F\$5 & \$F\$4 = DATE RANGE I HAVE SPECIFIED
'Error Report Data'!\$C\$2:\$C\$25000 = VALUES WHERE I WANT A DISTINCT COUNT (HAS BLANK CELLS)

What I want is give me the distinct count of values in Column C where the dates in Column A are in range of column F

Thank you!
Bill

2. ## Re: Counting distinct values with blank cells produces #DIV/0!

What type of data is in 'Error Report Data'!\$C\$2:\$C\$25000?

Is it text? Numbers? Could be both? Something else?

3. ## Re: Counting distinct values with blank cells produces #DIV/0!

Hello Bill,

This formula should work for any type of data in column C, and it ignores blanks

=SUM(IF(FREQUENCY(IF(('Error Report Data'!\$A\$2:\$A\$25000<=\$F\$5)*('Error Report Data'!\$A\$2:\$A\$25000>=\$F\$4)*('Error Report Data'!\$C\$2:\$C\$25000<>""),MATCH('Error Report Data'!\$C\$2:\$C\$25000,'Error Report Data'!\$C\$2:\$C\$25000,0)),ROW('Error Report Data'!\$C\$2:\$C\$25000)-ROW('Error Report Data'!\$C\$2)+1),1))

confirm with CTRL+SHIFT+ENTER

If column C is numeric (or dates) you can use a shorter version, i.e.

=SUM(IF(FREQUENCY(IF(('Error Report Data'!\$A\$2:\$A\$25000<=\$F\$5)*('Error Report Data'!\$A\$2:\$A\$25000>=\$F\$4),'Error Report Data'!\$C\$2:\$C\$25000),'Error Report Data'!\$C\$2:\$C\$25000),1))

also array entered

4. ## Re: Counting distinct values with blank cells produces #DIV/0!

Thank very much Tony and daddylonglegs.

This array formula worked perfectly

=SUM(IF(FREQUENCY(IF(('Error Report Data'!\$A\$2:\$A\$25000<=\$F\$5)*('Error Report Data'!\$A\$2:\$A\$25000>=\$F\$4),'Error Report Data'!\$C\$2:\$C\$25000),'Error Report Data'!\$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