Counting uniques in a colunm that has blanks & zeros from previous work sheets?

Question

I am trying count the number of unique names in column B on a worksheet. The worksheet that column B is located, copies the values from other worksheets in the same workbook. If there is a blank cell on the previous worksheet it shows as a zero on the worksheet where the formula is located. Names show as names. I am currently using {=SUM(1/COUNTIF(B1:B468,B1:B468))}. However I am not certain that is the correct formula. I am trying to count only unique names and exclude both the blanks & the zeroes. I have checked both similar questions proposed here & on Google. What I found was that they counted uniques with blanks, but not with blanks & zeroes.

Answer 1

By "counting uniques" I assume you don't want to count the same value twice.....in which case try this formula

=SUMPRODUCT((B1:B468<>0)/COUNTIF(B1:B468,B1:B468&""))

Answer 2

Barry Houdini's solution works, but I think this would be considerably more efficient (though that's not an issue if you only have ~500 records):

=SUM(IF(FREQUENCY(IF(B1:B468<>0,MATCH(B1:B468,B1:B468,0)),ROW(B1:B468)),1))

Entered as an array formula by typing Ctrl+Shift+Enter.

Also, regarding ImaginaryHuman072889's answer, the NOT(ISBLANK) test is redundant. This would do the exact same thing (albeit, as Barry Houdini pointed out, wouldn't account for unique values)

= SUMPRODUCT(--(B1:B468<>0))

Answer 3

I would do this:

= SUMPRODUCT((B1:B468<>0)*NOT(ISBLANK(B1:B468)))

This counts all entries in the cell range that are non-blank and non-zero.