jana
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
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
The "remove duplicity" we already used them. We need a function so not to depend on the menu options.
TestSummOther.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just tried the formula but doesnt work.
What do you mean by CTRL+SHIFT+ENTER?
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).
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).
ASKER
Ok got it, you mean CTRL-SHIFT-ENTER right after typing.
Worked!
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.
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?
To close this question, please advice on:
1. You have "ROW(A2:A5191)-ROW(A2)+1)"
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.
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.
ASKER
Thanx!
ASKER
Thanx!
=SUMPRODUCT((A1:A7<>"")/CO
where A1:A7 contains the list