use a function for filtered data



I am trying to count items in a spreadsheet using the following formula
=SUM(IF(FREQUENCY(A3:A83,A3:A83)>0,1)) when I filter the amount comes up the
same--how can I incorporate the subtotal function in here to get a proper
number. The data in the range is week numbers that correspond with activities
performed each day. I need to filter how many of the weeks the activity is

Ashish Mathur


First of all, we must thank Biff for sharing this solution. This is the
formula to count the unique values in a filtered range.


If this works for you, the kindly post back and thank Biff for the same.


Ashish Mathur
Microsoft Excel MVP

T. Valko


That formula will count unique numbers.

Try this array formula** :


** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

T. Valko

This is the formula to count the unique
values in a filtered range.

That is a generic formula that will count *both* text and numbers.

The OP posted that formula so I'm assuming they want to count the unique
numbers in the filtered list. If the range might contain both text and
numbers then you need to write the formula to specifically count the numbers
only. If the range will only contain numbers then the generic formula should

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question