I have used COUNTIFS() formula in Google Sheet. Whenever I insert a new row in the worksheet the range of the COUNTIFS formula gets updated.

For e.g if my formula is


and I insert one row the formula will become


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 didn't 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.

2 Answers

player0 On Best Solutions

use INDIRECT to lock it down for good:

=COUNTIFS(INDIRECT("Detail!C2:C10"), "CR", INDIRECT("Detail!C2:C10"), "Delivered")
Tom Sharpe On

If you wanted it to change from C2:C10 to C2:C11, you could mix an indirect reference with a normal one: