Filtered Counting
 

Hello I found an excellent formula to count unique values in a given column
and it even modifies the count when they are filtered. Here's the formula I
am using for this...

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0))


I want to modify this to actually count all non-blanks (even duplicates) BUT
I want to subtract all the entries of "No Data Here" and "Sold Out"

So The formula gives me 1114 Unique entries and it adjusts when filtered.
But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold Out"
even when filtered.

Is this possible?

Thanks in advance.

Filtered Counting
 

Not sure I follow you on this.

You want the count of all entries in the filtered or unfiltered range less
the count of "No Data Here" and "Sold Out" ?

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Hello I found an excellent formula to count unique values in a given
column
and it even modifies the count when they are filtered. Here's the formula
I
am using for this...

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0))


I want to modify this to actually count all non-blanks (even duplicates)
BUT
I want to subtract all the entries of "No Data Here" and "Sold Out"

So The formula gives me 1114 Unique entries and it adjusts when filtered.
But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold
Out"
even when filtered.

Is this possible?

Thanks in advance.

Filtered Counting
 

Biff, Thanks for your reply... Yes, That is exactly what I am trying to do.

"T. Valko" wrote:

Not sure I follow you on this.

You want the count of all entries in the filtered or unfiltered range less
the count of "No Data Here" and "Sold Out" ?

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Hello I found an excellent formula to count unique values in a given
column
and it even modifies the count when they are filtered. Here's the formula
I
am using for this...

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0))


I want to modify this to actually count all non-blanks (even duplicates)
BUT
I want to subtract all the entries of "No Data Here" and "Sold Out"

So The formula gives me 1114 Unique entries and it adjusts when filtered.
But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold
Out"
even when filtered.

Is this possible?

Thanks in advance.

Filtered Counting
 

Try this. This example formula is counting the filtered or unfiltered range
B3:B21. Adapt to suit.

=SUBTOTAL(3,B3:B21)-SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B21,ROW(B3:B21)-ROW(B3),0,1)),--(ISNUMBER(MATCH(B3:B21,{"sold
out","no data here"},0))))

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Biff, Thanks for your reply... Yes, That is exactly what I am trying to
do.

"T. Valko" wrote:

Not sure I follow you on this.

You want the count of all entries in the filtered or unfiltered range
less
the count of "No Data Here" and "Sold Out" ?

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Hello I found an excellent formula to count unique values in a given
column
and it even modifies the count when they are filtered. Here's the
formula
I
am using for this...

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0))


I want to modify this to actually count all non-blanks (even
duplicates)
BUT
I want to subtract all the entries of "No Data Here" and "Sold Out"

So The formula gives me 1114 Unique entries and it adjusts when
filtered.
But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold
Out"
even when filtered.

Is this possible?

Thanks in advance.

Filtered Counting
 

Awesome!! It Worked!

Thank You Sooo Much!



"T. Valko" wrote:

Try this. This example formula is counting the filtered or unfiltered range
B3:B21. Adapt to suit.

=SUBTOTAL(3,B3:B21)-SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B21,ROW(B3:B21)-ROW(B3),0,1)),--(ISNUMBER(MATCH(B3:B21,{"sold
out","no data here"},0))))

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Biff, Thanks for your reply... Yes, That is exactly what I am trying to
do.

"T. Valko" wrote:

Not sure I follow you on this.

You want the count of all entries in the filtered or unfiltered range
less
the count of "No Data Here" and "Sold Out" ?

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Hello I found an excellent formula to count unique values in a given
column
and it even modifies the count when they are filtered. Here's the
formula
I
am using for this...

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0))


I want to modify this to actually count all non-blanks (even
duplicates)
BUT
I want to subtract all the entries of "No Data Here" and "Sold Out"

So The formula gives me 1114 Unique entries and it adjusts when
filtered.
But I want all 2876 entries minus 27 "No Data Here" and minus 56 "Sold
Out"
even when filtered.

Is this possible?

Thanks in advance.

Filtered Counting
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Awesome!! It Worked!

Thank You Sooo Much!



"T. Valko" wrote:

Try this. This example formula is counting the filtered or unfiltered
range
B3:B21. Adapt to suit.

=SUBTOTAL(3,B3:B21)-SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B21,ROW(B3:B21)-ROW(B3),0,1)),--(ISNUMBER(MATCH(B3:B21,{"sold
out","no data here"},0))))

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Biff, Thanks for your reply... Yes, That is exactly what I am trying to
do.

"T. Valko" wrote:

Not sure I follow you on this.

You want the count of all entries in the filtered or unfiltered range
less
the count of "No Data Here" and "Sold Out" ?

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Hello I found an excellent formula to count unique values in a given
column
and it even modifies the count when they are filtered. Here's the
formula
I
am using for this...

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3 000)-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""),IF(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$3000 )-ROW($D$6),,1)),MATCH($D$6:$D$3000,$D$6:$D$3000,0), ""))0))


I want to modify this to actually count all non-blanks (even
duplicates)
BUT
I want to subtract all the entries of "No Data Here" and "Sold Out"

So The formula gives me 1114 Unique entries and it adjusts when
filtered.
But I want all 2876 entries minus 27 "No Data Here" and minus 56
"Sold
Out"
even when filtered.

Is this possible?

Thanks in advance.