Using Frequency() to grab unique values

[weeble33]

Hello,

I'm trying to count only unique values/text in a column. I came across the formula:

Please Login or Register  to view this content.

Now this works fine, but I don't understand why I can't turn it into a sumif(s) or countif(s):

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Array entered or not, I keep getting: "The formula you typed contains an error". As far as I've learned, Frequency() returns an array and if the bin is the same range as the data array, 0 will be returned for each duplicated value. I'd like as small (and preferrably non-array) a formula as I can get because this will be entered into other sum/count-ifs.

[XOR LX]

Hi,

The first of your formulas does not require array-entry.

Unfortunately, the SUMIF(S)/COUNTIF(S) family does not accept anything other than actual range references for the range criteria.

Another (non-array) alternative is:

=SUMPRODUCT((B2:B10<>"")*(1/COUNTIF(B2:B10,B2:B10)))

Regards

[weeble33]

Hmmm, nothing other than actual ranges or named ranges. First thing I thought of was to try using the INDIRECT() function...I feel like it's helped me out in the past with similar issues but didn't quite do it here. That sumproduct may be the best bet. I just need to watch my already high calculation time (hence the desire for a non-array formula).