Filtered Counting


R

Rob

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$3000)-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.
 
Ad

Advertisements

T

T. Valko

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

T. Valko

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))))
 
R

Rob

Awesome!! It Worked!

Thank You Sooo Much!



T. Valko said:
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))))
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top