Dynamic subtotals and eliminate dupes

[ttbbgg]

Working with a large spreadsheet. I will engage Autofilter and want to add a
section at the bottom for subtotals pertaining to the filtered data shown.
What formula(s) would I use for that since it would change for each filter
selection?

Also, I want to put in a dynamic count for an array, however there will be
duplicates. How do I eliminate a count with dupes in an array?

 

[bpeltzer]

If you turn on the filter and make the filter active (that is, make a
selection from one of the filtered columns), the autosum button will use the
subtotal function rather than sum. If you want to enter it yourself, the
formula is =subtotal(9,range), where range is the entire (unfiltered) range;
subtotal will exclude cells that get filtered out by your autofilter
selections.

 

[Domenic]

For a unique count on filtered data, try...

=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),,1)),A
2:A10),IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),,1)),A2:A10)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[ttbbgg]

OK, no more error, but I am getting a count of 0. Help!

 

[ttbbgg]

Sorry, this isn't working. I keep getting a formula error. I am seeing
array references throughout. Do I simply change the array reference and keep
it consistent for every entry? Please note that my row count is 2 through to
15042.

 

[Domenic]

OK, no more error, but I am getting a count of 0. Help!

Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, instead
of just ENTER. In other words, type the formula, press the CONTROL and
SHIFT keys down, while these two keys are pressed down, press ENTER.
Excel will place braces {} around the formula, indicating that you've
entered the formula correctly.

Hope this helps!

 

[ttbbgg]

I think I am getting a value of 0 because FREQUENCY only recognizes numbers
and not text. I have text strings that I need to count for unique entry.

 

[Domenic]

In that case, try...

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

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!