Finding descrete valuse

[Steve M]

I have a spreadsheet with a column containing 192 cells (column A below).
Some of the cells contain the same value. For each cell there is a related
cell in the same row (Column B below). As I change other values in my
spreadsheet the 192 cells (Column A)may change. I would like to sum the
values in the related cells (column B)for each of the 192 cells that contain
the same value. I tried to use the "small" function in conjunction with the
"sumif" but the "small" function does not differenciate between repeat
values.

Example of the problem
A B
1 17.5 15%
2 17.5 10%
3 18 5%
4 19 20%
5 19 20%
6 19 30%

I would like to have the following results:

7 17.5 25%
8 18 5%
9 19 70%

 

[Ron Coderre]

Hi, Steve
I'm thinking that a Pivot Table is the easiest way to summarize the Col_A
values.

You'll need column headings to do that.
A1: Category
B1: Pct

Try this:

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Category field here
DATA: Drag the Pct field here
If it doesn't list as Sum of Pct...dbl-click it and set it to Sum
Click [OK]
Select where you want the Pivot Table...and you're done.

That will list each distinct Category and the sum of corresponding Pcts.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[RagDyeR]

Try this:

In A7 enter:
=A1

In A8 enter this *array* formula:
=IF(ISERROR(MATCH(0,COUNTIF(A$7:A7,$A$1:$A$6&""),0)),"",INDEX(IF(ISBLANK($A$1:$A$6),"",$A$1:$A$6),MATCH(0,COUNTIF(A$7:A7,$A$1:$A$6&""),0)))

Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE, copy down.

In B7 enter:
=SUMIF($A$1:$A$6,A7,$B$1:$B$6)
And copy down.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have a spreadsheet with a column containing 192 cells (column A below).
Some of the cells contain the same value. For each cell there is a related
cell in the same row (Column B below). As I change other values in my
spreadsheet the 192 cells (Column A)may change. I would like to sum the
values in the related cells (column B)for each of the 192 cells that contain
the same value. I tried to use the "small" function in conjunction with the
"sumif" but the "small" function does not differenciate between repeat
values.

Example of the problem
A B
1 17.5 15%
2 17.5 10%
3 18 5%
4 19 20%
5 19 20%
6 19 30%

I would like to have the following results:

7 17.5 25%
8 18 5%
9 19 70%

 

[Steve M]

Ron:
Thanks for the input. Yes it does help and meets my needs. Don't know why
I didn't think of it myself.
Thanks again
Steve

Hi, Steve
I'm thinking that a Pivot Table is the easiest way to summarize the Col_A
values.

You'll need column headings to do that.
A1: Category
B1: Pct

Try this:

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Category field here
DATA: Drag the Pct field here
If it doesn't list as Sum of Pct...dbl-click it and set it to Sum
Click [OK]
Select where you want the Pivot Table...and you're done.

That will list each distinct Category and the sum of corresponding Pcts.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

I have a spreadsheet with a column containing 192 cells (column A below).
Some of the cells contain the same value. For each cell there is a related
cell in the same row (Column B below). As I change other values in my
spreadsheet the 192 cells (Column A)may change. I would like to sum the
values in the related cells (column B)for each of the 192 cells that contain
the same value. I tried to use the "small" function in conjunction with the
"sumif" but the "small" function does not differenciate between repeat
values.

Example of the problem
A B
1 17.5 15%
2 17.5 10%
3 18 5%
4 19 20%
5 19 20%
6 19 30%

I would like to have the following results:

7 17.5 25%
8 18 5%
9 19 70%