How to count unique cells in a matrix while exluding errors and blanks

Question

I have Gantt chart and I would like to count the number of unique cells that are not blank and not error values. This is an example of what my data looks like.

         308940   #NUM!    603460
827881   827881   827881   827881
         348490   #NUM!    887288
         #NUM!    #NUM!    602713
         888567   888567   888567
ABD532   ABD532   ABD532   ABD532

I have tried a few formulas but to no avail.

{=SUMPRODUCT(1/COUNTIF('Post Gantt'!H2:K7,'Post Gantt'!H2:K7))}

This formula counts everything but it does not exclude errors or blank cells. I also tried a formula with frequency match but that would return errors I think about the blank cells.

I would like the formula to achieve the following value for the example data set above.

tigeravatar tigeravatar · Accepted Answer · 2019-10-23T14:33:54

As an array formula:

=SUMPRODUCT((IFERROR(H2:K7&"","")<>"")/COUNTIF(H2:K7,H2:K7&""))

The IFERROR will get rid of errors, and then using the &"" will prevent Excel from interpreting blank cells as 0, and then <>"" will ignore blanks.

EDIT: With the sheet names:

=SUMPRODUCT((IFERROR('Post Gantt'!H2:K7&"","")<>"")/COUNTIF('Post Gantt'!H2:K7,'Post Gantt'!H2:K7&""))

How to count unique cells in a matrix while exluding errors and blanks

2 Answers