Formula Format Settings

Question

Hi All,&nbsp;&nbsp;I have encountered and issue with the settings of my formulas I use for work around data manipulation.&nbsp;When using VLOOKUPS and COUNTIFS the table or range in which the formula looks up or counts a value against shifts down as I apply the formula to the below cells. Meaning: &nbsp;=COUNTIFS(Sheet3!I2:I133,B2,Sheet3!V2:V133,B2)Sheet3!I2:I133 = the first range I look the value of B2 against&nbsp;Sheet3!V2:V133 = the range I will count how many times B2 shows in this range&nbsp;&nbsp;When formula is applied to the cells below as I have more than one row of data to count, this happens;=COUNTIFS(Sheet3!I2:I133,B2,Sheet3!V2:V133,B2)=COUNTIFS(Sheet3!I3:I134,B3,Sheet3!V3:V134,B3)=COUNTIFS(Sheet3!I2:I133,B4,Sheet3!V2:V133,B4)This means that both ranges (tables) are shifting down as its applied, therefore when you get to row 10, the formula excludes the first 9 values in the second range....Ultimately giving an inaccurate count...The B value is the only figure to increase&nbsp;Same issue applies when using VLOOKUPS.... the table it searches its value against shifts down, therefore excluding values..&nbsp;&nbsp;How can this be fixed?....&nbsp;

pascalkteam · Answer

Looks like you should use an absolute reference. Let's assume that this formula Sheet3!I2:I133 should always start at I2, even if you copy it a down a couple of rows you have to make row 2 an absolute reference by adding $ to the formula like this: Sheet3!I$2:I133

twifoo · Answer

Alex159&nbsp;When you are copying down rows, lock the row references of your range by preceding them with dollar ($) signs, like this:&nbsp;=COUNTIFS(Sheet3!I$2:I$133,B2,Sheet3!V$2:V$133,B2)

alex159 · Answer

Awesome thanks for the advice, any reason why this could be happening and not to other members of my team?

pascalkteam · Answer

Since this is standard excel behaviour it should be the same for all users

Forum Discussion

Formula Format Settings

4 Replies

Resources