Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding descrete valuse
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% |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding descrete valuse
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 "Steve M" wrote: 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% |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding descrete valuse
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),MA TCH(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! ================================================== === "Steve M" wrote in message ... 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% |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding descrete valuse
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 "Ron Coderre" wrote: 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 "Steve M" wrote: 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% |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a link source. | Excel Worksheet Functions | |||
Finding duplicate data in a worksheet | Excel Discussion (Misc queries) | |||
Finding Asymptotes from a set of data in Excel | Excel Worksheet Functions | |||
Finding Asymptotes from a set of data in Excel | Excel Discussion (Misc queries) | |||
Finding Duplicate Names from Different Lists... | Excel Discussion (Misc queries) |