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