Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
group cells.......
Use subtotals (go to menu /data /subtotals)
RADO "R-M" wrote in message ... Hi I've following information wanted to group by first column and show the sum of quantities: a b c ---- ----- ------ 123 10.8 123 20.2 123 44 124 2 124 11 124 45 The desired result: a b c ---- ----- ------ 123 10.8 75 - sum(10.8+20.2+44) 123 20.2 123 44 124 2 58 - sum(2+11+45+0) 124 11 124 45 124 0 note: I don't want to write sum(a1:a3) , ... because it depends on the number of rows has the same value. any help would greatly appricieted. -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
group cells.......
Hi
I've following information wanted to group by first column and show the sum of quantities: a b c ---- ----- ------ 123 10.8 123 20.2 123 44 124 2 124 11 124 45 The desired result: a b c ---- ----- ------ 123 10.8 75 - sum(10.8+20.2+44) 123 20.2 123 44 124 2 58 - sum(2+11+45+0) 124 11 124 45 124 0 note: I don't want to write sum(a1:a3) , ... because it depends on the number of rows has the same value. any help would greatly appricieted. -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
group cells.......
The following VBA code will do what you want
Sub STots() Dim Rw 'Row being evaluated Dim Var 'The value in column A Dim Srw 'the 1st row containing VAR Dim Stot 'sub-total of values in column B Rw = 1: Srw = 1 Var = Cells(Rw, 1) Do Do Stot = Stot + Cells(Rw, 2) Rw = Rw + 1 Loop While Cells(Rw, 1) = Var Cells(Srw, 3) = Stot Stot = 0: Srw = Rw: Var = Cells(Rw, 1) Loop Until Cells(Rw, 1) = "" End Sub The above assumes that your data list starts in cell A1. If not, adjust as necessary. regards, Don -- "R-M" wrote in message ... Hi I've following information wanted to group by first column and show the sum of quantities: a b c ---- ----- ------ 123 10.8 123 20.2 123 44 124 2 124 11 124 45 The desired result: a b c ---- ----- ------ 123 10.8 75 - sum(10.8+20.2+44) 123 20.2 123 44 124 2 58 - sum(2+11+45+0) 124 11 124 45 124 0 note: I don't want to write sum(a1:a3) , ... because it depends on the number of rows has the same value. any help would greatly appricieted. -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
group cells.......
If your data starts in row 1, then the formula for C1 would be:
=SUMIF(A:A,"="&A1,B:B) Then in subsequent rows, use: =IF(A2=A1,"",SUMIF(A:A,"="&A2,B:B)) -- Dianne In , R-M typed: I've following information wanted to group by first column and show the sum of quantities: a b c ---- ----- ------ 123 10.8 75 - sum(10.8+20.2+44) 123 20.2 123 44 124 2 58 - sum(2+11+45+0) 124 11 124 45 124 0 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
group cells.......
I should have said:
In row 2 enter =IF(A2=A1,"",SUMIF(A:A,"="&A2,B:B)) and copy it down. -- Dianne In , Dianne typed: If your data starts in row 1, then the formula for C1 would be: =SUMIF(A:A,"="&A1,B:B) Then in subsequent rows, use: =IF(A2=A1,"",SUMIF(A:A,"="&A2,B:B)) I've following information wanted to group by first column and show the sum of quantities: a b c ---- ----- ------ 123 10.8 75 - sum(10.8+20.2+44) 123 20.2 123 44 124 2 58 - sum(2+11+45+0) 124 11 124 45 124 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you name a group of cells? | Excel Discussion (Misc queries) | |||
Match a group of cells with another group of cells | Excel Worksheet Functions | |||
Copy Data from One Group of Cells to Another Group | Charts and Charting in Excel | |||
from a group of cells.find average of cells containing values | Excel Discussion (Misc queries) | |||
copy group of cells to another group of cells using "IF" in third | Excel Worksheet Functions |