# Formula to Sum Cells when value changes in another Column

#### WednesdayC

##### Board Regular
Hi All

I found the following formula which almost gives the results I require:-

<style>table { }.font5 { color: black; font-size: 9pt; font-weight: 700; font-style: normal; text-decoration: none; font-family: Arial, serif; }.font6 { color: black; font-size: 9pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial, serif; }td { padding: 0px; color: windowtext; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial, serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl102 { background: rgb(216, 228, 188) none repeat scroll 0% 0%; }.xl103 { font-family: Calibri; }.xl104 { font-family: Calibri; }</style>
 =IF(A3<>A2,SUM(\$M\$2:M2)-SUM(\$N\$1:N1),"")

<colgroup><col style="width:75pt" width="75"> </colgroup><tbody>
</tbody>

However, this places the result on the last entry of N before the values in column A changes.

I need the result to be in the first cell of N per group i.e. the first entry of any batch of identical cell values in A, and I can't work out how to adapt this.

I do not wish to use the sub-total function.

Many thanks

Wednesday

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Kaper

##### Board Regular
The formula you've shown is probably intended to be insterted in some cell in row 2 and copied down
you may try:
Code:
[COLOR=#000000][FONT=Arial]=IF(A2<>A1,SUM(\$M\$1:M1)-SUM(\$N\$1:N1)+sumif(A:A,A2,M:M)-sumif(A:A,A2,N:N),"")[/FONT][/COLOR]
and copy it down
This assumess that values in column A are grouped, so after
x
x
x
y
y
y
z
z
z
you do not get x again in column A (all x-es ate at the top positions).

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
In cell N2 try this, copied down after adjusting the \$1000 if necessary to be something below the last row of your data.

=IF(A2=A1,"",SUM(M2:M\$1000)-SUM(N3:N\$1000))

Note that it will not show the correct results until you have copied it down column N.

Last edited:

#### WednesdayC

##### Board Regular
Hi Kaper

I am sure your formula works, but since posting, I found another formula which is a lot simpler for me to understand!

It is as follows:- <style>table { }.font5 { color: black; font-size: 9pt; font-weight: 700; font-style: normal; text-decoration: none; font-family: Arial, serif; }.font6 { color: black; font-size: 9pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial, serif; }td { padding: 0px; color: windowtext; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial, serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl102 { font-family: Calibri; }.xl103 { font-family: Calibri; }.xl104 { font-size: 10pt; font-family: Courier; vertical-align: middle; }</style>

<style>table { }.font5 { color: black; font-size: 9pt; font-weight: 700; font-style: normal; text-decoration: none; font-family: Arial, serif; }.font6 { color: black; font-size: 9pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial, serif; }td { padding: 0px; color: windowtext; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial, serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl102 { font-family: Calibri; }.xl103 { font-family: Calibri; }.xl104 { font-size: 10pt; font-family: Courier; vertical-align: middle; }</style>
<style>table { }.font5 { color: black; font-size: 9pt; font-weight: 700; font-style: normal; text-decoration: none; font-family: Arial, serif; }.font6 { color: black; font-size: 9pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial, serif; }td { padding: 0px; color: windowtext; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial, serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl102 { font-family: Calibri; }.xl103 { font-family: Calibri; }.xl104 { font-size: 10pt; font-family: Courier; vertical-align: middle; }</style>
 =IF(A2=A1,"",SUMIF(A:A,A2,N:N)) So I have decided to go with this one. Thank you for your time Regards Wednesday

<colgroup><col style="width:75pt" width="75"> </colgroup><tbody>
</tbody>

<colgroup><col style="width:75pt" width="75"> </colgroup><tbody>
</tbody>

<colgroup><col style="width:75pt" width="75"> </colgroup><tbody>
</tbody>

#### WednesdayC

##### Board Regular

Hi Peter

Thank you very much for your help.

I have decided to use the following formula, as it is a little shorter. I found it online after posting:-

<style>table { }.font5 { color: black; font-size: 9pt; font-weight: 700; font-style: normal; text-decoration: none; font-family: Arial, serif; }.font6 { color: black; font-size: 9pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial, serif; }td { padding: 0px; color: windowtext; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial, serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl102 { font-family: Calibri; }.xl103 { font-family: Calibri; }.xl104 { font-size: 10pt; font-family: Courier; vertical-align: middle; }</style>
 =IF(A2=A1,"",SUMIF(A:A,A2,N:N)) Regards Wednesday

<colgroup><col style="width:75pt" width="75"> </colgroup><tbody>
</tbody>

#### Peter_SSs

##### MrExcel MVP, Moderator
I have decided to use the following formula, ..
That's fine if it works for your circumstances. We didn't have any sample data to go on so was not sure if all your groups are unique.
For example, if it was possible to have data like below, your formula gives incorrect results for the coloured groups whereas mine would treat each of those groups individually.
Anyway, I guess you don't have that situation so it doesn't matter.

Excel Workbook
AMNO
1
2a1178
3a2
4a3
5a2
6b54646
7b1
8b40
9c444
10a5179
11a4
12e166
13e2
14e3
Sum Groups

Last edited:

#### WednesdayC

##### Board Regular
Hi Peter

I see what you mean. Yes, all my groups are unique. I will keep hold of your formula though, as I may encounter a situation where the groups aren't unique.

Wednesday

Replies
4
Views
259
Replies
14
Views
842
Replies
6
Views
329
Replies
3
Views
344
Replies
0
Views
229