Jack-P-Winner
Member
already answered
Last edited:
Can I copy and paste a countif formula with a $ involved
- Thread starter Jack-P-Winner
- Start date
NARAYANK991
Excel Ninja
Hi Larry ,
Given the formula you have posted , you can enter it in the following different ways :
=COUNTIF(link!$C$2:$C$101,1)
=COUNTIF(link!$C$2:$C101,1)
=COUNTIF(link!$C$2:C$101,1)
=COUNTIF(link!$C2:C101,1)
The above are 4 combinations possible with one particular way of referencing the start point ; since there are 4 different ways of referencing the start point , it means there are 16 possible ways you can enter this formula !
How you should be entering it depends on what you want to do.
Using the $ sign before any of the elements means that element will not change when you copy or drag.
To take an example , the most rigid way of entering it is the first highlighted in red , where nothing will change whether you drag / copy down or across , since the $ sign precedes all the elements.
The most changeable would be the following :
=COUNTIF(link!C2:C101,1)
where the $ sign has not been used at all ; if you enter this formula in one cell , and copy it down to the cell below , it will change to :
=COUNTIF(link!C3:C102,1)
If you copy it across to the next column , it will change to :
=COUNTIF(link!D2:D101,1)
Thus depending on which element(s) you do not want should change , and depending on whether you will be copying downwards , across , or both , you need to insert the $ sign in the proper places.
Narayan
Given the formula you have posted , you can enter it in the following different ways :
=COUNTIF(link!$C$2:$C$101,1)
=COUNTIF(link!$C$2:$C101,1)
=COUNTIF(link!$C$2:C$101,1)
=COUNTIF(link!$C2:C101,1)
The above are 4 combinations possible with one particular way of referencing the start point ; since there are 4 different ways of referencing the start point , it means there are 16 possible ways you can enter this formula !
How you should be entering it depends on what you want to do.
Using the $ sign before any of the elements means that element will not change when you copy or drag.
To take an example , the most rigid way of entering it is the first highlighted in red , where nothing will change whether you drag / copy down or across , since the $ sign precedes all the elements.
The most changeable would be the following :
=COUNTIF(link!C2:C101,1)
where the $ sign has not been used at all ; if you enter this formula in one cell , and copy it down to the cell below , it will change to :
=COUNTIF(link!C3:C102,1)
If you copy it across to the next column , it will change to :
=COUNTIF(link!D2:D101,1)
Thus depending on which element(s) you do not want should change , and depending on whether you will be copying downwards , across , or both , you need to insert the $ sign in the proper places.
Narayan
Jack-P-Winner
Member
Hi Narayan, I need to use the =COUNTIF(link!$C2:$C101,1). I probably should have worded what I needed different perhaps. I want to be able to put lets say put this formula in cell A:1 =COUNTIF(link!$C2:$C101,1) and drag it down to A:1000 so that the last digit is the only thing that will change. so it would look like this
=COUNTIF(link!$C2:$C101,1)
=COUNTIF(link!$C2:$C101,2)
=COUNTIF(link!$C2:$C101,3)
=COUNTIF(link!$C2:$C101,4)
and so on
can this be done?
=COUNTIF(link!$C2:$C101,1)
=COUNTIF(link!$C2:$C101,2)
=COUNTIF(link!$C2:$C101,3)
=COUNTIF(link!$C2:$C101,4)
and so on
can this be done?
NARAYANK991
Excel Ninja
Hi Larry ,
Which means that you do not want either the C2 or the C101 to change as you drag down ?
If that is so , the first change you should make is use the $ sign before the row component , as in :
=COUNTIF(link!C$2:C$101,1)
This ensures that as you drag / copy this formula downwards , irrespective of where you may have entered it to start with , the formula will not change.
Now , you need to decide whether after entering this formula in any particular cell , you will be copying it across to any other column , and if so would you want it to change or remain the same.
If you want it to change as follows :
=COUNTIF(link!D$2:D$101,1)
=COUNTIF(link!E$2:E$101,1)
then it is OK the way it has been shown above in blue , otherwise you will need to insert the $ sign before the column component also.
However , apart from all this , if you want the last parameter to change as you copy it downwards , instead of using 1 , 2 , 3 ,... use the ROW($A1) construct , as in :
=COUNTIF(link!C$2:C$101,ROW($A1))
When you copy this downwards , the $A1 will become $A2 , $A3 , $A4 ,... and the ROW function will convert it to 2 , 3 , 4 ,...
Narayan
Which means that you do not want either the C2 or the C101 to change as you drag down ?
If that is so , the first change you should make is use the $ sign before the row component , as in :
=COUNTIF(link!C$2:C$101,1)
This ensures that as you drag / copy this formula downwards , irrespective of where you may have entered it to start with , the formula will not change.
Now , you need to decide whether after entering this formula in any particular cell , you will be copying it across to any other column , and if so would you want it to change or remain the same.
If you want it to change as follows :
=COUNTIF(link!D$2:D$101,1)
=COUNTIF(link!E$2:E$101,1)
then it is OK the way it has been shown above in blue , otherwise you will need to insert the $ sign before the column component also.
However , apart from all this , if you want the last parameter to change as you copy it downwards , instead of using 1 , 2 , 3 ,... use the ROW($A1) construct , as in :
=COUNTIF(link!C$2:C$101,ROW($A1))
When you copy this downwards , the $A1 will become $A2 , $A3 , $A4 ,... and the ROW function will convert it to 2 , 3 , 4 ,...
Narayan
Jack-P-Winner
Member
Correct I not want either the C2 or the C101 to change as I drag it down
Let me clarify, I want the end digit to change as I drag
=COUNTIF(link!C$2:C$101,1)
Let me clarify, I want the end digit to change as I drag
=COUNTIF(link!C$2:C$101,1)
Last edited:
NARAYANK991
Excel Ninja
Hi Larry ,
Re-quoted from my earlier post :
However , apart from all this , if you want the last parameter to change as you copy it downwards , instead of using 1 , 2 , 3 ,... use the ROW($A1) construct , as in :
=COUNTIF(link!C$2:C$101,ROW($A1))
When you copy this downwards , the $A1 will become $A2 , $A3 , $A4 ,... and the ROW function will convert it to 2 , 3 , 4 ,...
Narayan
Re-quoted from my earlier post :
However , apart from all this , if you want the last parameter to change as you copy it downwards , instead of using 1 , 2 , 3 ,... use the ROW($A1) construct , as in :
=COUNTIF(link!C$2:C$101,ROW($A1))
When you copy this downwards , the $A1 will become $A2 , $A3 , $A4 ,... and the ROW function will convert it to 2 , 3 , 4 ,...
Narayan
Jack-P-Winner
Member
wonderful, thank you Narayan!!!