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

**antexity** · 04-10-2015, 06:15 PM

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

**Tony Valko** · 04-10-2015, 06:21 PM

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

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

**daddylonglegs** · 04-10-2015, 06:36 PM

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

**antexity** · 04-14-2015, 09:00 AM

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))