Excel code help.

[vegyjones]

I have this formula

=SUMIF(Sheet1!B1:B25,"=Description A", Sheet1!D1: D25)

Basically sheet A has a list of stuff in column B with their values in column D.
I'm using sheet 2 to calculate the total for one item through rows 1 -25

The forumla works great for that.

However, if I extend the area, I have to change the number of rows in the calculation for each item I'm doing the calculation for.

Is there a way of automatically selecting the number of rows.

Say rows 1-25 represent the month January. If I extend the number of rows for January transactions to 30, is there something I can add that would automatically adjust the caluculations?

[wb]

I have the formula Vegy. It's an IF command...

IF someone here reads this - "description"=mathare,

answer=will be provided


:ermmm

[vegyjones]

I have the formula Vegy. It's an IF command...

IF someone here reads this - "description"=mathare,

answer=will be provided


:ermmm

:D He's not logged on.
Doesn't the fool know he's on 24 hour call out? :laugh

[TheOldhamWhisper]

If I understand what you are trying to do, then:


=SUMIF(Sheet1!B:B,"=Description A", Sheet1!D:D)

should do the trick.

[Win2Win]

If we got back to 2003 when Vegy joined and asked him about =SUMIF(Sheet1!B1:B25,"=Description A", Sheet1!D1: D25) ......he would have passed out :laugh .....maybe hope for mankind after all...:rolleyes:

[mathare]

Oldham's solution looks good to me

[MattR]

Mat or Oldham will correct me if I'm wrong here but if you had something else further down in column B and didn't want to pick that up in the calculaton (as B:B would) then if you insert a row inside the 25 on sheet 2 then the formula would auto adjust to b1:b26, adding one on the end however won't do this. Anyway that's just if you have something else in column b.

[vegyjones]

I think I understand that Matt,

so If rows 26-50 contained Fenruary's data, but I didn't want that included, Oldham's suggestion won't quite be what I'm after, is that right?

[MattR]

Yes that's it. So for example if on sheet 2 column b you have 1-25 as January's data but below was february, then the B:B part would add everything in column b. Your b1:b25 would ofcourse ignore you adding on b26 etc, but if you insert a row anywhere from b1:b25 then the formula will auto adjust to b1:b26 or whatever depending how many rows you added. B1:B27 for two etc etc. Just something I found out by accident and thought "that's useful to know!"

So your formula will be fine as it was as long as you just add in rows between the boundaries (doesn't matter where) it will auto expand

[mathare]

You can also define a named range and use that instead - I think (I've never done it that way in Excel but I do it a lot in VBA).

Just checked - you can.

Select the cells you want to define as your range, B1:B25 for example, and then select Insert -> Name -> Define and enter a name for the range, eg JanData. Note that it will use absolute cell references (including the $ symbol) but you can change these (delete the $ symbol). Then you can change your formula to something like:

=SUMIF(JanData,"=Description A", Sheet1!D1: D25)

Why would you do this? If the range changes, say you need to add in some more rows, then you can go to Insert -> Name -> Define select JanData and change the range in that one place and it'll update all your formulae that use that JanData range. Thus you just need to make the changes in one place and all your formulae will automatically update.

As with most things, there are several ways you can do what you want so it's just a case of finding out which suits you best.

[vegyjones]

You can also define a named range and use that instead - I think (I've never done it that way in Excel but I do it a lot in VBA)..


Thanks for that Mat. Was something I'd actually used before when trying to format an automated Footy SIm teamsheet but had forgotten all about that!

Wonderful :D