• 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.

countif changes the range

jb

jb

Member
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.
 

Attachments

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!
 
NARAYANK991 said:
Hi ,

Can you specify what change takes place ?

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

Narayan
Click to expand...
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.
 

Attachments

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
 
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.
 

Attachments

In J2 drag copied down:

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

In N2 drag copied down:

=SUMPRODUCT(--(b!$C$1:$G$6=M2))
 
Last edited:
You must log in or register to reply here.
Back
Top