Relative cell reference

[Karnik]

Hello Experts,

I use following function:-

=COUNTIF(FUL!CE10:CE993,11)+COUNTIF(FUL!CE10:CE993,111)+COUNTIF(FUL!CE10:CE993,1111)

When I insert in worksheet "Ful!" at row no 10, the above function (which is in different sheet) shows cell value as CE11 instead CE10

I tried locking $CG$10 but it did not work

Any guidance?
Thanks
Karnik

[Pete_UK]

If you don't want the references to change when you insert or delete rows, then you will have to do it like this:

=COUNTIF(indirect("FUL!CE10:CE993"),11)+COUNTIF(indirect("FUL!CE10:CE993"),111)+COUNTIF(indirect("FUL!CE10:CE993"),1111)

Hope this helps.

Pete

[Moo the Dog]

Do you have an example spreadsheet that you can upload? I tried your formula and it worked fine with test data on my end. However, you may have something completely different.

- Vince

[Karnik]

Thanks Pete_UK
It Works,
Cheers
Karnik