Formula to Sum Cells when value changes in another Column

[WednesdayC]

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> [TABLE="width: 75"]
<colgroup><col style="width:75pt" width="75"> </colgroup><tbody>[TR]
[TD="class: xl102, width: 75"]=IF(A3<>A2,SUM($M$2:M2)-SUM($N$1:N1),"")
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Kaper]

The formula you've shown is probably intended to be insterted in some cell in row 2 and copied down
you may try:

[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).

 

[Peter_SSs]

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.

 

[WednesdayC]

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> [TABLE="width: 75"]
<colgroup><col style="width:75pt" width="75"> </colgroup><tbody>[TR]
[TD="class: xl104, width: 75, align: right"]
<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> [TABLE="width: 75"]
<colgroup><col style="width:75pt" width="75"> </colgroup><tbody>[TR]
[TD="class: xl104, width: 75, align: right"]<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> [TABLE="width: 75"]
<colgroup><col style="width:75pt" width="75"> </colgroup><tbody>[TR]
[TD="class: xl104, width: 75"]=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
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[WednesdayC]

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> [TABLE="width: 75"]
<colgroup><col style="width:75pt" width="75"> </colgroup><tbody>[TR]
[TD="class: xl104, width: 75"]=IF(A2=A1,"",SUMIF(A:A,A2,N:N))



Regards

Wednesday
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

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
	A	M	N	O
1
2	a	1	17	8
3	a	2
4	a	3
5	a	2
6	b	5	46	46
7	b	1
8	b	40
9	c	4	4	4
10	a	5	17	9
11	a	4
12	e	1	6	6
13	e	2
14	e	3
Sum Groups

 

[WednesdayC]

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