Learn-to-send-email-via-google-script-html-no-server: Locking Range in a Google Sheet Formula

[Sree0808]

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.

[nelsonic]

@Sree0808 this is really a question for a Google forum.
(_this tutorial is not meant to be exhaustive of spreadsheet skills..._)

If you shared more detail of what you are trying to achieve it would help others to help you resolve it.

[Sree0808]

Thanks for responding @nelsonic .
I am using the formula =COUNTIF(range, criterion) in my google sheet.
When I insert a new row in the worksheet, the range section of the COUNTIF() formula gets 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.

[nelsonic]

@Sree0808 while I wish I could help answer your question its best re-posted on
https://superuser.com with the appropriate tags to get a quick response from the Google Apps user community.
(Please share a link to your question so we can all learn the answer... Thanks!)

[Sree0808]

I got the below answer from another forum and would like to share it here for the benefit of anyone seeking the same answer.
We can use INDIRECT() formula to lock the range
In my example the formula was COUNTIFS(Detail!C2:C10,"CR",Detail!D2:D10,"Delivered").

If we want the range to be C2:C10 and D2:D10 even after insertion of one row i.e. if we want the formula to keep the starting row and end row same as before, then use the below :
=COUNTIFS(INDIRECT("Detail!C2:C10"), "CR", INDIRECT("Detail!C2:C10"), "Delivered")

If we want the range to be C2:C11 and D2:D11 after the insertion of one row i.e. if we want the formula to keep the starting row same but increase the last row, then use the below :
=COUNTIFS(INDIRECT("Detail!C2"):Detail!C10,"CR",INDIRECT("Detail!D2"):Detail!D10,"Delivered")

[Sree0808]

Thanks @nelsonic for responding