Omit Hidden Rows When Counting

[Diego Garcia]

In a database of names I use Filter- Advanced Filter - Unique records, to hide duplicated rows. Trouble is I don't know if there were any duplicated rows when it finishes. I would like to see the totals reflect this by not including them in the Countif function. Is it possible?

[iwrk4dedpr]

There is a "SUBTOTAL" function which will count only visible items. However, it does not have a countif parameter.


How are you hiding the duplicated rows? Do you have a flag value that indicates that a row is a duplicate?

[daddylonglegs]

If you were using a formula like

=COUNTIF(A1:A100,"x")

then to count "x"s in unfiltered rows only try

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(A1:A100,ROW(A1:A100)-ROW(A1)+1,,1))),--(A1:A100="x"))

[turtle44]

With your Advanced Filter, you could "Copy to" instead of "Filter in Place", then there won't be any hidden rows.

[iwrk4dedpr]

If you were using a formula like

=COUNTIF(A1:A100,"x")

then to count "x"s in unfiltered rows only try

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(A1:A100,ROW(A1:A100)-ROW(A1)+1,,1))),--(A1:A100="x"))

I think that DLL has got the better solution. I'd have suggested it but I've had difficulties on the intricasies of these compound sumproduct formulas.

[Diego Garcia]

Sorry I had used Counta. Tried countif(C187:C247,">A"), returns Volatile with the formula cell blank.

---

Auto Merged Post;

Just tried iwrk..'s subtotal which does the trick. There are that many duplicates I had to double check!
Thanks very much.

[Dave Hawley]

If you wish to continue using this free service.

Please be considerate of others who use the forum for searching. Your current Thread Title (which I will change) is non-reflective of your immediate problem.

In future, please take 1 minute of your time to read the text on the New Thread page.

Please take just 3 mins of your time to read: How To Get Your Question Answered...FAST! AND Anatomy Of A Good Thread Title

REMEMBER: Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will wrong and prevent someone from finding a solution to a simliar issue.