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

Mechanical 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)
``````
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
``````
1

Experts Exchange Solution brought to you by