Static Cell Refrences in Formulas

I have a formula that is looking at a range of cells on a second worksheet
in a workbook. The cell range is A2:A1000, the formula is looking at the
range, and counting the number of times a certain value appears.

=COUNTIF(Sheet2!A2:A1000,"THING")

There are currently 116 different values in this cell range that I need
statistics on, what I want to do is create one forumla and copy it 115 times
WITHOUT the cell numbers changing in relation to where the formula is
copied. For example, if I copy the formula from A1 to D20 on any given
worksheet, the formula will be changed to:

=COUNTIF(Sheet2!D21:D1019,"THING")

I know that I will have to edit each formula to change the search criteria,
but I don't want to have to spend the time changing the cell numbers on top
of the value to be counted, or creating a formula from scratch for each
value.

Can someone help me?

You can do both.

In A1:An, list your criteria.

In B1, add =COUNTIF(Sheet2!$A$2:$A$1000,$A1)

and copy down


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ben" <b_devoes@comcast.net> wrote in message
news:%23YCWUHdAGHA.1312@TK2MSFTNGP09.phx.gbl...
> I have a formula that is looking at a range of cells on a second worksheet
> in a workbook. The cell range is A2:A1000, the formula is looking at the
> range, and counting the number of times a certain value appears.
>
> =COUNTIF(Sheet2!A2:A1000,"THING")
>
> There are currently 116 different values in this cell range that I need
> statistics on, what I want to do is create one forumla and copy it 115
times
> WITHOUT the cell numbers changing in relation to where the formula is
> copied. For example, if I copy the formula from A1 to D20 on any given
> worksheet, the formula will be changed to:
>
> =COUNTIF(Sheet2!D21:D1019,"THING")
>
> I know that I will have to edit each formula to change the search
criteria,
> but I don't want to have to spend the time changing the cell numbers on
top
> of the value to be counted, or creating a formula from scratch for each
> value.
>
> Can someone help me?
>
>

Thank you for you help!

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:egQJkPdAGHA.832@tk2msftngp13.phx.gbl...
> You can do both.
>
> In A1:An, list your criteria.
>
> In B1, add =COUNTIF(Sheet2!$A$2:$A$1000,$A1)
>
> and copy down
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Ben" <b_devoes@comcast.net> wrote in message
> news:%23YCWUHdAGHA.1312@TK2MSFTNGP09.phx.gbl...
>> I have a formula that is looking at a range of cells on a second
>> worksheet
>> in a workbook. The cell range is A2:A1000, the formula is looking at the
>> range, and counting the number of times a certain value appears.
>>
>> =COUNTIF(Sheet2!A2:A1000,"THING")
>>
>> There are currently 116 different values in this cell range that I need
>> statistics on, what I want to do is create one forumla and copy it 115
> times
>> WITHOUT the cell numbers changing in relation to where the formula is
>> copied. For example, if I copy the formula from A1 to D20 on any given
>> worksheet, the formula will be changed to:
>>
>> =COUNTIF(Sheet2!D21:D1019,"THING")
>>
>> I know that I will have to edit each formula to change the search
> criteria,
>> but I don't want to have to spend the time changing the cell numbers on
> top
>> of the value to be counted, or creating a formula from scratch for each
>> value.
>>
>> Can someone help me?
>>
>>
>
>

Ben

=COUNTIF(Sheet2!$A$2:$A$1000,"THING")

Surround the references with the $ signs to lock the column and rows.

A1 is relative
$A1 is column absolute
A$1 is row absolute
$A$1 is row and column absolute

F2 to edit first formula then select the address and hit F4 to toggle through
the options.

Drag/copy that formula down 116 rows.

There may be a way to get the search criteria into each cell without editing
each formula.

Do you have the 116 "Things" in a list somewhere that could be addressed.

i.e. they are in a list in column B from B1:B116

In that case try this =COUNTIF(Sheet2!$A$2:$A$1000,B1)

Drag/copy down 116 rows. B1 will increment to follow your list.


Gord Dibben Excel MVP

On Thu, 15 Dec 2005 17:46:45 -0600, "Ben" <b_devoes@comcast.net> wrote:

>I have a formula that is looking at a range of cells on a second worksheet
>in a workbook. The cell range is A2:A1000, the formula is looking at the
>range, and counting the number of times a certain value appears.
>
>=COUNTIF(Sheet2!A2:A1000,"THING")
>
>There are currently 116 different values in this cell range that I need
>statistics on, what I want to do is create one forumla and copy it 115 times
>WITHOUT the cell numbers changing in relation to where the formula is
>copied. For example, if I copy the formula from A1 to D20 on any given
>worksheet, the formula will be changed to:
>
>=COUNTIF(Sheet2!D21:D1019,"THING")
>
>I know that I will have to edit each formula to change the search criteria,
>but I don't want to have to spend the time changing the cell numbers on top
>of the value to be counted, or creating a formula from scratch for each
>value.
>
>Can someone help me?
>

Thank you for your help!

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:fh04q19l6a9ch99umknm7jg7eehtcr5o50@4ax.com...
> Ben
>
> =COUNTIF(Sheet2!$A$2:$A$1000,"THING")
>
> Surround the references with the $ signs to lock the column and rows.
>
> A1 is relative
> $A1 is column absolute
> A$1 is row absolute
> $A$1 is row and column absolute
>
> F2 to edit first formula then select the address and hit F4 to toggle
> through
> the options.
>
> Drag/copy that formula down 116 rows.
>
> There may be a way to get the search criteria into each cell without
> editing
> each formula.
>
> Do you have the 116 "Things" in a list somewhere that could be addressed.
>
> i.e. they are in a list in column B from B1:B116
>
> In that case try this =COUNTIF(Sheet2!$A$2:$A$1000,B1)
>
> Drag/copy down 116 rows. B1 will increment to follow your list.
>
>
> Gord Dibben Excel MVP
>
> On Thu, 15 Dec 2005 17:46:45 -0600, "Ben" <b_devoes@comcast.net> wrote:
>
>>I have a formula that is looking at a range of cells on a second worksheet
>>in a workbook. The cell range is A2:A1000, the formula is looking at the
>>range, and counting the number of times a certain value appears.
>>
>>=COUNTIF(Sheet2!A2:A1000,"THING")
>>
>>There are currently 116 different values in this cell range that I need
>>statistics on, what I want to do is create one forumla and copy it 115
>>times
>>WITHOUT the cell numbers changing in relation to where the formula is
>>copied. For example, if I copy the formula from A1 to D20 on any given
>>worksheet, the formula will be changed to:
>>
>>=COUNTIF(Sheet2!D21:D1019,"THING")
>>
>>I know that I will have to edit each formula to change the search
>>criteria,
>>but I don't want to have to spend the time changing the cell numbers on
>>top
>>of the value to be counted, or creating a formula from scratch for each
>>value.
>>
>>Can someone help me?
>>