• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can someone explain the do's and don't about the command $

Jack-P-Winner

Jack-P-Winner

Member
I have been correcting literally 1000's of errors on my spreadsheets and have found the $ in places that I did not think they belong and then again none where I think they belong.

When do you put the $ in a condition? One of the formulas is:
=COUNTIF(Link!$I$2:$I$101,5)

or do I do it this way:
=COUNTIF(Link!I2:I101,6)

Thanks folks!!!
 
Thanks Hui, this is where my confusion comes in. I was hacked bad last Sept and I am going over my project cell by cell and find the following: I have different sections of numbers they are B1-B5, BB, 50+ and OBHITS. They are all treated as a individual number so I am trying to figure out which one is correct

B1-B5
=(COUNTIF(C2:C$101,O2)+COUNTIF(F2:F$101,O2)+COUNTIF(I2:I$101,O2)+COUNTIF(L2:L$101,O2)+COUNTIF(O2:O$101,O2)+COUNTIF(X2:X$101,O2))


The BB is like this
=COUNTIF($R$2:$R$101,R3)-COUNTIF($R2:$R$2,R3)


I noticed in the drawn numbers tab for the OBHITS is like this
=COUNTIF('Drawn Numbers'!$Q:$Q,5)

but the 50+ is like this
=COUNTIF(Lnk!F2:F101,5)
 
You use a $ when you want to lock a cell address for copying
$A$1 forces the cell to be copied as $A$1 regardless of where it is copied
$A1 Locks the Column value A but the row will change when the cell is copied vertically
A$1 Locks the Row value 1 but the Column will change when the cell is copied horizontally
A1 is unlocked and both will change when it is copied either vertically or horizontally

You can use any combinations of above in formulas or parts of formulas depending on the required results
 
You must log in or register to reply here.
Back
Top