Static Cell Refrences in Formulas

[Ben]

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!D211019,"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?

 

[Bob Phillips]

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)

 

[Gord Dibben]

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

 

[Ben]

Thank you for you help!

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]

Thank you for your help!

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