Validating Unique Values within a tab and Counting across multiple tabs

I have an Excel spreadsheet with 8 tabs of about 200 records in each tab.  I want to
1-For each tab scan the tab and determine if all values are unique for Column A, but ignore evaluating the value called "Filler" in Col A.  Note I am only interested if the values are unique within the tab that is in focus, I am not interested in determining if the values are unique across all tabs..
2-Count all cells in Column A across all 8 tabs starting on row 5.  This if there 100 cells in each tab then my expected value would be 800.  Maybe find last row and do a count function ?

Note I am familiar with a loop and realize I can loop thru each tab to count and find unique values, but I want to find out the most efficient way to do this...
upobDaPlayaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Counting across all the tabs is simple: use a 3D formula with either COUNT or COUNTA, depending on whether you want to count just numeric values or text and numbers.
=COUNT(Sheet1:Sheet8!A2:A$1000)
=COUNTA(Sheet1:Sheet8!A2:A$1000)

Open in new window

Detecting whether an individual value in column A is unique can be done with a COUNTIF function in a Conditional Format or Data validation:
=COUNTIF($A$2:$A$1000,$A2)=1

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
upobDaPlayaAuthor Commented:
Brilliant !  So Simple !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.