SUBTOTAL in an Array forumla - Counting unique & repeat values disregarding blanks

I have the following array formula that provides a summary of the number of unique customers and repeat customers.

Formula:

Please Login or Register  to view this content.

This works fine, but if there is a blank row it returns an error.

I've tried replacing SUM with SUBTOTAL instead (I think this handles blanks better, right?) but this doesn't work AT ALL even if there are no blanks.

What I would like is for it to give the number of unique text values, and the number of repeated text values (not the number of times they're repeated) while ignoring (not counting) blanks.

Hi,

Any chance of an actual workbook? Formulas with structured references are not so easy to mock up.

Regards

Thanks.

="Qty of Unique Customers: "&SUM(IF(FREQUENCY(IF([Customer]<>"",MATCH([Customer],[Customer],0)),ROW([Customer])-MIN(ROW([Customer]))+1)>0,1))&" | Qty of Repeat Customers: "&SUM(IF(FREQUENCY(IF([Customer]<>"",MATCH([Customer],[Customer],0)),ROW([Customer])-MIN(ROW([Customer]))+1)>1,1))

The repeated use of the MATCH clause is not necessary and causes undue extra calculation. I am not sure why it is still recommended by some sources.

Regards

Originally Posted by -AJ-

Works perfectly. Thanks fellow Yorkshireman/Yorkshirewomen (small world)

You're welcome!

Yorkshire IS the world!