Is there way to totalize in a column and extract one occurrence of each in excel 2010

Is there a function in excel that we can point it to a column and tell us how many items is in occurrence.  For example, in the column below, we would like if a function would tells that there is 4 contracts (is like using DISTINCT in MsSql script).

Columna
Contrat1
Contrat2
Contrat2
Contrat3
Contrat4
Contrat4
Contrat4

Open in new window

rayluvsAsked:
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.

NBVCCommented:
You can use a formula like:

=SUMPRODUCT((A1:A7<>"")/COUNTIF(A1:A7,A1:A7&""))

where A1:A7 contains the list
0
als315Commented:
You can use remove duplicates button on Data tab:
remove duplicates step 1remove duplicates step 2remove duplicates step 3
0
rayluvsAuthor Commented:
The formula does not return the correct value.  Please attached excel (i have places the values and applied your formula... also used SUBTOTAL to count the result in order to validate the data.)

The "remove duplicity" we already used them.  We need a function so not to depend on the menu options.
TestSummOther.xlsx
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

NBVCCommented:
Try:

=COUNT(1/FREQUENCY(IF(A2:A5191<>"",MATCH(A2:A5191,A2:A5191,0)),ROW(A2:A5191)-ROW(A2)+1))

confirmed with CTRL+SHIFT+ENTER not just ENTER.
0

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
rayluvsAuthor Commented:
Just tried the formula but doesnt work.

What do you mean by CTRL+SHIFT+ENTER?
0
NBVCCommented:
you can also create a pivot table.

First put a header title in A1, the go to Insert Pivot Table.  Select where you want the table, then drag the title to Rows Area, then drag same title to Sum Values area.  This gives a listing of all unique items and their individual counts.  You can then count the unique values using the COUNTA() function and subtracting 3 (for header, Totals row and blanks row).
0
rayluvsAuthor Commented:
Ok got it, you mean CTRL-SHIFT-ENTER right after typing.


Worked!
0
NBVCCommented:
After you enter the formula, hold the CTRL and SHIFT keys down, then press ENTER.  You should see { } brackets around the formula.  It is called an Array Formula.
0
rayluvsAuthor Commented:
Appreciate your info on Pivot, but the formula works just fine for what we need.

To close this question, please advice on:

1. You have "ROW(A2:A5191)-ROW(A2)+1)" in the formula.  As we see it, if wee need
     to use this formula with anothe excel, we should alwyas place the ROW value on
     the first cell of the range. Correct?

2. Why CTRL-SHIFT-ENTER?

3. Finally, if the data within the range changes, do we have to always
   CTRL-SHIFT-ENTER? or can we make this automatically?
0
NBVCCommented:
1. Yes, correct.

2. Because this is the way Excel understands that you are entering an array formula.  More on Array formulas:  http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

3. You only need to re-confirm if you touch the formula cell.  If you insert or remove rows within the range, the formula should adjust itself.  If you change the values of cells in the range, then formula should be ok.  Only if you change the formula for any reason, do you need to reconfirm it.
0
rayluvsAuthor Commented:
Thanx!
0
rayluvsAuthor Commented:
Thanx!
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
Microsoft Excel

From novice to tech pro — start learning today.