Stable formula

Let say I have this formula,
=SUMIF('TSJ Student List'!C3:C118,"=4",'TSJ Student List'!A3:A118)

My question is how can I use only rows C3:C118 and A3:A118 and the only
changes is "=4" to 5,6 ....because when I copy or drag it will change to
C4:C119, A4:A119 ..... I try to use $C3:C118$ or $c3$:$c118$ but it does not
work. Please guide me. Thanks.

Syahira wrote:
> Let say I have this formula,
> =SUMIF('TSJ Student List'!C3:C118,"=4",'TSJ Student List'!A3:A118)
> My question is how can I use only rows C3:C118 and A3:A118 and the only
> changes is "=4" to 5,6 ....because when I copy or drag it will change to
> C4:C119, A4:A119 ..... I try to use $C3:C118$ or $c3$:$c118$ but it does not
> work.

I think you want $C$3:$C$C118 and $A$3:$A$118.

You are getting your $'s in the wrong place.

Edit the cell, click on say each range C3:C118, then use F4 to toggle =

through the various $ options


Steve

On Fri, 28 Jul 2006 03:48:01 +0100, Syahira =

<Syahira@discussions.microsoft.com> wrote:

> Let say I have this formula,
> =3DSUMIF('TSJ Student List'!C3:C118,"=3D4",'TSJ Student List'!A3:A118)=

>
> My question is how can I use only rows C3:C118 and A3:A118 and the onl=
y
> changes is "=3D4" to 5,6 ....because when I copy or drag it will chang=
e to
> C4:C119, A4:A119 ..... I try to use $C3:C118$ or $c3$:$c118$ but it do=
es =

> not
> work. Please guide me. Thanks.

It depends on *how* you want the criteria to change.

This allows you to enter the criteria in a particular cell, so that it can
be changed by manually entering another number in that cell, and you
wouldn't need to revise the formula itself.

Say the criteria cell is A1, then try this:

=SUMIF('TSJ Student List'!C3:C118,A1,'TSJ Student List'!A3:A118)


However, if you're going to copy the formula down a column, and you wish for
the criteria to increment as the formula is copied down, try this, with the
criteria starting at "4", and increasing by "1" as it's copied down:

=SUMIF('TSJ Student List'!C$3:C$118,ROWS($1:4),'TSJ Student List'!A$3:A$118)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Syahira" <Syahira@discussions.microsoft.com> wrote in message
news:6302C583-98E4-4D4F-AC78-A2BDD4D3F0E3@microsoft.com...
> Let say I have this formula,
> =SUMIF('TSJ Student List'!C3:C118,"=4",'TSJ Student List'!A3:A118)
>
> My question is how can I use only rows C3:C118 and A3:A118 and the only
> changes is "=4" to 5,6 ....because when I copy or drag it will change to
> C4:C119, A4:A119 ..... I try to use $C3:C118$ or $c3$:$c118$ but it does
> not
> work. Please guide me. Thanks.