HELP!! SUMIF QUESTION

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kahuna - 14 Jun 2007 12:12 GMT

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?

Reply to this Message

kahuna - 14 Jun 2007 12:24 GMT

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

> x 10
> x 11 21
[quoted text clipped - 11 lines]
> 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?

Reply to this Message

Toppers - 14 Jun 2007 12:37 GMT

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.

> 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
[quoted text clipped - 15 lines]
> > 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?

Reply to this Message

kahuna - 14 Jun 2007 13:43 GMT

great thanks!

> try:
>
[quoted text clipped - 21 lines]
> > > 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?

Reply to this Message

Mike H - 14 Jun 2007 12:34 GMT

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

> x 10
> x 11 21
[quoted text clipped - 11 lines]
> 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?

Reply to this Message