Function help?
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
The -2 refers to rows and means go up 2 rows.
If it was 2 it would mean go down 2 rows.
If it was 2 it would mean go down 2 rows.
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
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!
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
It shouldn't matter as the MATCH part of the formula takes care of that.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!
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
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?
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?
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
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
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
Is that all it does....?!
Not very clever at all!
Thanks for that S!
Not very clever at all!
Thanks for that S!
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
What you probably want is sum(If) formula rather than sumif.
ie 2 formulas combined rather than just one.
ie 2 formulas combined rather than just one.
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
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....
- Mystery
- FISO Knight
- Posts: 13816
- Joined: 13 Oct 2005, 18:33
- Location: Just about here
- FS Record: 116th in TFF 05/06
- FPL:
- Contact:
Function help?
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.
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.
-
- Dumbledore
- Posts: 7976
- Joined: 17 Aug 2006, 21:24
- FS Record: FPL: Not as good as it was, but still very respectable.
- FPL:
Re: Function help?
If you are at all unsure about sumif, look at countif first.
{} = array formulae = death. Avoid.
{} = array formulae = death. Avoid.
-
- Dumbledore
- Posts: 7976
- Joined: 17 Aug 2006, 21:24
- FS Record: FPL: Not as good as it was, but still very respectable.
- FPL:
Re: Function help?
If you are at all unsure about sumif, look at countif first.
{} = array formulae = death. Avoid.
{} = array formulae = death. Avoid.
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
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!!
- Jester
- Sir Jedi Jesterlot
- Posts: 25201
- Joined: 13 Oct 2005, 18:28
- Location: Back at FISODAS Tower!
- FS Record: TFC 2008 T20 winner; EggCup Div 1 06/07; Two-Foot Cup 2018; S4 EPL Cup 2020
- Contact:
Re: Function help?
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!!
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!!
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Function help?
Every time you edit one you have to press control+shift+enter rather than just enterJester 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!!
- Mystery
- FISO Knight
- Posts: 13816
- Joined: 13 Oct 2005, 18:33
- Location: Just about here
- FS Record: 116th in TFF 05/06
- FPL:
- Contact:
Function help?
My TFF spreadsheet relies on 'em now!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!!
View Latest: 1 Day View Your posts