# Modify Excel Formula

Modify this formula below to include all contents of column H in the Apptracker tab https://docs.google.com/spreadsheets/d/17LeToNWtIC5uqo8QnXsDHYof7cisAakKct2ZBtK6QfU/edit#gid=1254353415 regardless of whether the rows are hidden, but without duplicates.

``````=ArrayFormula(Apptracker!H:H)
``````
###### Who is Participating?

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

Microsoft Excel ExpertCommented:
put the following in B2

``````=UNIQUE(Apptracker!H:H)
``````
Author Commented:
Perfect with respect to no duplicates, but does still leave an empty cell.
Microsoft Excel ExpertCommented:
Try this:
=UNIQUE( FILTER( app tracker!H:H ; NOT(ISBLANK(app tracker!H:H)) ) )
Or this
=UNIQUE(FILTER(app tracker!H:H;NOT(ISNA(app tracker!H:H))))
Author Commented:
Option 1 works as adapted perfectly so did not test option 2.
``````=UNIQUE( FILTER( Apptracker!H3:H, NOT(ISBLANK(Apptracker!H3:H)) ) )
``````
How can I produce the number of rows under column A where each unique company apppears?
Microsoft Excel ExpertCommented:
then you put somewhere  this formula =COUNTA(Apptracker!A:A)-1
Author Commented:
Which cell?
Microsoft Excel ExpertCommented:
wherever you want to see the number of count result,  put in any cell
Author Commented:
It should count the number of unique companies in each row of the tab named Companies.  Please advise further.
Microsoft Excel ExpertCommented:
here is the formula =COUNTUNIQUE(B2:B)
Microsoft Excel ExpertCommented:
you can put this in any cell except column B
Author Commented:
Getting closer.  Still not quite right.  Here is a screencast of how I experienced it http://screencast.com/t/NTEllHYhrax
Microsoft Excel ExpertCommented:
ok then this formula

=COUNTIFS(Apptracker!H:H,B2)

Experts Exchange Solution brought to you by