countif changes the range

[jb]

Hi experts,
I have attached an excel sheet "testing.xls" .
I have data in a table from a2 to g7.
Some codes are written in c2 to g7.
I have written all possible codes from i2 to i6.
I have written countif formula in j2 to j6.
Now if I drag the data a2:g2 to say for example a9:g9, then it changes the range
of countif.
Is there any way to prevent this? I mean dragging data out of table should not change the range of countif.

 

[AliGW]

Use $ to anchor the array:

$A$2:$G$2

 

[jb]

Use $ to anchor the array:

$A$2:$G$2

I have used $ in formula madam.

 

[NARAYANK991]

Hi ,

Can you specify what change takes place ?

I don't see any change happening when I drag A2:G2 to row #9.

Narayan

 

[SirJB7]

Hi, jb!
It's right that COUNTIF's range could get changed, since you'd be moving boundary cells of that range. But in the uploaded file you used ranges from row 3 to 7 and if you're changing row 2 formulas related to 3:7 won't get changed, of course.
Regards!

 

[jb]

Hi ,

Can you specify what change takes place ?

I don't see any change happening when I drag A2:G2 to row #9.

Narayan

Sir, I am uploading file once again. If I drag the data a2:g2 to say for example a9:g9, then it is not changing the range of countif.

But If I drag the data a7:g7 to say for example a9:g9, then it is changing the range of countif.

Is there any way to prevent this? I mean dragging data out of table should not change the range of countif.

 

[NARAYANK991]

Hi ,

This is Excel's normal behaviour.

Suppose a formula refers to a range A2:J37 ; if you now insert a row before row 2 , essentially you have two rows which are outside the range covered by the formula ; for the formula to reflect this , the references have to change to A3:J38 , whether you have used relative referencing or absolute referencing.

Similarly , with the same formula reference , suppose you insert a row anywhere between row 2 and row 37 ; surely Excel is supposed to change the reference to A2:J38 ?

So also if you insert columns.

When you drag a row which is within a formula reference , Excel assumes that it should change references and acts accordingly.

If you do not want this default behaviour , then you should think of using the INDIRECT function in your formula , which will ensure that references do not change.

Narayan

 

[bosco_yip]

.......
Is there any way to prevent this? I mean dragging data out of table should not change the range of countif.

Maybe….

In J2, copied down :

=COUNTIF($C$2:OFFSET($G$2,,,6),I2)

Regards
Bosco

 

[jb]

Maybe….

In J2, copied down :

=COUNTIF($C$2:OFFSET($G$2,,,6),I2)

Regards
Bosco

Now, dragging a2:g2 to outside table say a9:g9 changes the range and gives incorrect calculation in j column.

 

[bosco_yip]

Now, dragging a2:g2 to outside table say a9:g9 changes the range and gives incorrect calculation in j column.

Then, changed to :

=COUNTIF(OFFSET($A$1,1,2,6,5),I2)

Regards
Bosco

 

[jb]

Then, changed to :

=COUNTIF(OFFSET($A$1,1,2,6,5),I2)

Regards
Bosco

Thanks a lot. It worked.

 

[jb]

Sir,
I used indirect function. And it worked.
=COUNTIF(INDIRECT("C2"):INDIRECT("G7"),I2)

Also I got reply from one member bosco_yip. His solution also worked perfectly.
=COUNTIF(OFFSET($A$1,1,2,6,5),I2)

Now I am trying to keep table on sheet b and calculation on sheet a.
For that I am trying to revise formula which includes sheet name in formula.
But indirect function is not allowing me to include sheet name b in formula.

I am attaching excel sheet with the formula I tried in cell n2 to n7.

Help me.

 

[AliGW]

In J2 drag copied down:

=SUMPRODUCT(--($C$2:$G$7=I2))

In N2 drag copied down:

=SUMPRODUCT(--(b!$C$1:$G$6=M2))