How to copy countif formula without changing cell #s?

[MsCynic]

Hi guys, I often need to count names and data in a worksheet. I use the countif function to establish, for example, how many times Amy, Carla, Tanya and Bob's names appear in the sheet and from this I calculate a staff incentive.

This is how it looks: =COUNTIF(A1:A107,"*amy")

I want to be able to drag and copy this formula onto several cells below, changing only the name, but Excel always increases the cell numbers so I end up with:

=COUNTIF(A4:A111,"*bob"

Is there a way to drag and copy this formula without changing the cell numbers?

Thanks in advance for advice,
Jo

 

[swinglow]

put dollar signs around your cell references:

=COUNTIF($A$1:$A$107,"*amy")

that's called absolute reference. The dollar sign in front of the Column locks the column, and in front of the row locks the row.

Play around with different combinations (such as A1:$A$107) and fill your formula in different directs to see what affect it has. Absolute references and different combos of them are extremely useful.

When you're in the formula bar, pushing F4 automatically makes the cell reference absolute. Pushing it repeatedly will cycle you through the different combinations

 

[rob_craft]

Remved missed the bit above already covered my comments

 

[MsCynic]

Thanks Rob, I appreciate your help.
Jo