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

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

1
player0 On Best Solutions

use INDIRECT to lock it down for good:

=COUNTIFS(INDIRECT("Detail!C2:C10"), "CR", INDIRECT("Detail!C2:C10"), "Delivered")
1
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:

=countifs(indirect("Detail!C2"):Detail!C10,"CR",indirect("Detail!D2"):Detail!D10,"Delivered")