FISO

The -2 refers to rows and means go up 2 rows.
If it was 2 it would mean go down 2 rows.

Surprised wrote:The -2 refers to rows and means go up 2 rows.
If it was 2 it would mean go down 2 rows.

I thought it should be something like that.
Does the formula require the items in each sheet being in the same order then?


I'm having an early finish today, so can't play anymore today. Will try again tomorrow!

Jester wrote:

Surprised wrote:The -2 refers to rows and means go up 2 rows.
If it was 2 it would mean go down 2 rows.

I thought it should be something like that.
Does the formula require the items in each sheet being in the same order then?


I'm having an early finish today, so can't play anymore today. Will try again tomorrow!

It shouldn't matter as the MATCH part of the formula takes care of that.

I'm somewhat disappointed in myself but I couldn't get to grips with the whole offset/match thing....



SO! I found another way of doing it - lots of copy/pasting and a whole new summary sheet, but I got where I needed to be. Just hope no-one else ever has to understand it!



Todays quickie problem:

=SUMIF($B$49:$B$60168,1,$L$49:$L$60168)


Can someone put the above into words so I can understand what it's doing?
I think it says 'put the total of col B in here if there are values to add up in col B, otherwise enter the total of column L'

Am I close?

Jester wrote:I'm somewhat disappointed in myself but I couldn't get to grips with the whole offset/match thing....



SO! I found another way of doing it - lots of copy/pasting and a whole new summary sheet, but I got where I needed to be. Just hope no-one else ever has to understand it!



Todays quickie problem:

=SUMIF($B$49:$B$60168,1,$L$49:$L$60168)


Can someone put the above into words so I can understand what it's doing?
I think it says 'put the total of col B in here if there are values to add up in col B, otherwise enter the total of column L'

Am I close?

Not really
SUMIF just says add things up that meet certain criteria.
Here is says look at B49 to B60168 and if there is a 1 in any cell then add up the corresponding cells in L49 to L60168

Is that all it does....?!
Not very clever at all!

Thanks for that S!

What you probably want is sum(If) formula rather than sumif.
ie 2 formulas combined rather than just one.

No - this one is on a sheet I've inherited. Just couldn't work out what it was doing. I thought the 1 must refer to 'true' or 'false' or something....

Nah - that's the deal with SUMIF. The Help file puts it best but essentially it's syntax is:-

SUMIF (if I look here, and this is true, I add these up)

If you're inheriting work watch out for formulae with curly brackets around them. They sometimes mean something slightly different.

If you are at all unsure about sumif, look at countif first.

{} = array formulae = death. Avoid.

If you are at all unsure about sumif, look at countif first.

{} = array formulae = death. Avoid.

hancockjr wrote:If you are at all unsure about sumif, look at countif first.

{} = array formulae = death. Avoid.

A SumIf as an array enables multiple sumifs. I love 'em!!

I dabbled in array formulas the other day but as I make a lot of changes using cntrl+H, I gave up because I couldn't edit them that way!


My other work is getting so heavy at the moment that the playing with spreadsheets part has taken a back seat. If the cricket is boring tomorrow I might have a little go from the boundary though!!

Jester wrote:I dabbled in array formulas the other day but as I make a lot of changes using cntrl+H, I gave up because I couldn't edit them that way!


My other work is getting so heavy at the moment that the playing with spreadsheets part has taken a back seat. If the cricket is boring tomorrow I might have a little go from the boundary though!!

Every time you edit one you have to press control+shift+enter rather than just enter

Surprised wrote:

hancockjr wrote:If you are at all unsure about sumif, look at countif first.

{} = array formulae = death. Avoid.

A SumIf as an array enables multiple sumifs. I love 'em!!

My TFF spreadsheet relies on 'em now!