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.

"ttbbgg" wrote:

> 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?

[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!

In article <4E6E63F4-1006-41E9-9BFB-DFA3EB137AF6@microsoft.com>,
ttbbgg <ttbbgg@discussions.microsoft.com> wrote:

> 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?

[ttbbgg]

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

"Domenic" wrote:

> 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!
>
> In article <4E6E63F4-1006-41E9-9BFB-DFA3EB137AF6@microsoft.com>,
> ttbbgg <ttbbgg@discussions.microsoft.com> wrote:
>
> > 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?
>

[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" wrote:

> 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!
>
> In article <4E6E63F4-1006-41E9-9BFB-DFA3EB137AF6@microsoft.com>,
> ttbbgg <ttbbgg@discussions.microsoft.com> wrote:
>
> > 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?
>

[Domenic]

In article <4A975ED6-79CA-4C56-A927-45828768798D@microsoft.com>,
ttbbgg <ttbbgg@discussions.microsoft.com> wrote:

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

"ttbbgg" wrote:

> OK, no more error, but I am getting a count of 0. Help!
>
> "Domenic" wrote:
>
> > 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!
> >
> > In article <4E6E63F4-1006-41E9-9BFB-DFA3EB137AF6@microsoft.com>,
> > ttbbgg <ttbbgg@discussions.microsoft.com> wrote:
> >
> > > 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?
> >

[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!

In article <52F3035A-016E-44F4-8022-DD1B2ED664BD@microsoft.com>,
ttbbgg <ttbbgg@discussions.microsoft.com> wrote:

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