use a function for filtered data

[debl]

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
done.

 

[Ashish Mathur]

Hi,

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


=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A3:A83)-ROW(A2),)),MATCH(A3:A83,A3:A83,0)),ROW(A3:A83)-ROW(A2)))

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

=SUM(IF(FREQUENCY(A3:A83,A3:A83)>0,1))

That formula will count unique numbers.

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A3,ROW(A3:A83)-ROW(A3),)),A3:A83),A3:A83),1))

** 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
work.

 

[debl]

Thank you Ashish! This worked as well as Biff's solution!

 

[Ashish Mathur]

Hi,

Thank you. Once again Biff deserves the credit for this one - I learnt this
from him

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

You're welcome. Thanks for the feedback!