Formula to Sum Cells when value changes in another Column

WednesdayC

Board Regular
Joined
Nov 7, 2010
Messages
186
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.

Can this be achieved please?

Many thanks

Wednesday
 

Some videos you may like

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
Joined
Mar 14, 2014
Messages
230
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
Joined
May 28, 2005
Messages
46,253
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 7, 2010
Messages
186
Hi Kaper

Thank you for your reply.
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
Joined
Nov 7, 2010
Messages
186

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
46,253
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 7, 2010
Messages
186
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.

Thanks again for your help.

Wednesday
 

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,640
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top