Locking Range in a Google Sheet Formula #135

New issue

Closed

opened 2025-10-14 16:30:20 -06:00 by navan · 0 comments

navan commented

2025-10-14 16:30:20 -06:00

Owner

Originally created by @Sree0808 on 5/11/2019

I have used COUNTIFS() formula in Google Sheet.Whenever I insert a new row in the worksheet the range of the COUNTIFS formula get updated. For e.g if my formula is COUNTIFS(Detail!C2:C10,"CR",Detail!D2:D10,"Delivered") and I insert one row the formula will become COUNTIFS(Detail!C3:C10,"CR",Detail!D3:D10,"Delivered"). As such the newly inserted row is outside the range of COUNTIFS() formula and I get an undesired result.

I have tried appending $ to the column part of the range to lock but it didnt work i.e. Detail!C$2:C10

Could you suggest a way to lock the range "Detail!C2:C10" regardless of how many rows are newly added.

*Originally created by @Sree0808 on 5/11/2019* I have used COUNTIFS() formula in Google Sheet.Whenever I insert a new row in the worksheet the range of the COUNTIFS formula get updated. For e.g if my formula is COUNTIFS(Detail!C2:C10,"CR",Detail!D2:D10,"Delivered") and I insert one row the formula will become COUNTIFS(Detail!C3:C10,"CR",Detail!D3:D10,"Delivered"). As such the newly inserted row is outside the range of COUNTIFS() formula and I get an undesired result. I have tried appending $ to the column part of the range to lock but it didnt work i.e. Detail!C$2:C10 Could you suggest a way to lock the range "Detail!C2:C10" regardless of how many rows are newly added.