Link to home
Start Free TrialLog in
Avatar of jana
janaFlag for United States of America

asked on

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

Avatar of NBVC
NBVC
Flag of Canada image

You can use a formula like:

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

where A1:A7 contains the list
You can use remove duplicates button on Data tab:
User generated imageUser generated imageUser generated image
Avatar of jana

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of NBVC
NBVC
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

ASKER

Just tried the formula but doesnt work.

What do you mean by CTRL+SHIFT+ENTER?
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).
Avatar of jana

ASKER

Ok got it, you mean CTRL-SHIFT-ENTER right after typing.


Worked!
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.
Avatar of jana

ASKER

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?
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.
Avatar of jana

ASKER

Thanx!
Avatar of jana

ASKER

Thanx!