x 10
x 11 21
y 12
y 10 22

I`m using SUMIF to return the sub total values for x and y in column C
above, however I want only to show the values 21 and 22 above in C2 and C4
only and not anything in C1 and C3...basically so I can copy the formulas
down and not have to manually amend....

One solution is to include =IF(A1=A2,0,SUMIF( etc, which is great, but when
I insert a new row with another value for x, then row, the A1=A2 bit becomes
A1=A3, which is no good...B
The idea is that I want to maintain a table with subtotals for x and y and
have the ability to add in further rows....anybody have any ideas?

just to say im using SUMIF as oposed to a basic SUM(B2:B3) as it becomes
difficult to maintain when copying and inserting new rows as I have to keep
changing the range, I want something more automated.....thks

"kahuna" wrote:

x 10
x 11 21
y 12
y 10 22

I`m using SUMIF to return the sub total values for x and y in column C
above, however I want only to show the values 21 and 22 above in C2 and C4
only and not anything in C1 and C3...basically so I can copy the formulas
down and not have to manually amend....

One solution is to include =IF(A1=A2,0,SUMIF( etc, which is great, but when
I insert a new row with another value for x, then row, the A1=A2 bit becomes
A1=A3, which is no good...B
The idea is that I want to maintain a table with subtotals for x and y and
have the ability to add in further rows....anybody have any ideas?

I'm sure i've misunderstood here but anyway. Why not have the formulas

=SUMIF(A:A,"x",B:B) in C2
=SUMIF(A:A,"y",B:B) in C4

Add any new data to the bottom of cols A & B and then sort them. You formula
will remain in C2 and C4.

Mike

"kahuna" wrote:

x 10
x 11 21
y 12
y 10 22

I`m using SUMIF to return the sub total values for x and y in column C
above, however I want only to show the values 21 and 22 above in C2 and C4
only and not anything in C1 and C3...basically so I can copy the formulas
down and not have to manually amend....

One solution is to include =IF(A1=A2,0,SUMIF( etc, which is great, but when
I insert a new row with another value for x, then row, the A1=A2 bit becomes
A1=A3, which is no good...B
The idea is that I want to maintain a table with subtotals for x and y and
have the ability to add in further rows....anybody have any ideas?

try:

=IF(COUNTIF($A$1:A1,A1)=COUNTIF(A:A,A1),IF(SUMIF(A :A,A1,B:B)=0,"",SUMIF(A:A,A1,B:B)),"")

Copy down as far as you think your data will require.

"kahuna" wrote:

just to say im using SUMIF as oposed to a basic SUM(B2:B3) as it becomes
difficult to maintain when copying and inserting new rows as I have to keep
changing the range, I want something more automated.....thks

"kahuna" wrote:

x 10
x 11 21
y 12
y 10 22

I`m using SUMIF to return the sub total values for x and y in column C
above, however I want only to show the values 21 and 22 above in C2 and C4
only and not anything in C1 and C3...basically so I can copy the formulas
down and not have to manually amend....

One solution is to include =IF(A1=A2,0,SUMIF( etc, which is great, but when
I insert a new row with another value for x, then row, the A1=A2 bit becomes
A1=A3, which is no good...B
The idea is that I want to maintain a table with subtotals for x and y and
have the ability to add in further rows....anybody have any ideas?

great thanks!


"Toppers" wrote:

try:

=IF(COUNTIF($A$1:A1,A1)=COUNTIF(A:A,A1),IF(SUMIF(A :A,A1,B:B)=0,"",SUMIF(A:A,A1,B:B)),"")

Copy down as far as you think your data will require.

"kahuna" wrote:

just to say im using SUMIF as oposed to a basic SUM(B2:B3) as it becomes
difficult to maintain when copying and inserting new rows as I have to keep
changing the range, I want something more automated.....thks

"kahuna" wrote:

x 10
x 11 21
y 12
y 10 22

I`m using SUMIF to return the sub total values for x and y in column C
above, however I want only to show the values 21 and 22 above in C2 and C4
only and not anything in C1 and C3...basically so I can copy the formulas
down and not have to manually amend....

One solution is to include =IF(A1=A2,0,SUMIF( etc, which is great, but when
I insert a new row with another value for x, then row, the A1=A2 bit becomes
A1=A3, which is no good...B
The idea is that I want to maintain a table with subtotals for x and y and
have the ability to add in further rows....anybody have any ideas?