Modifying an existing formula to use a dynamic range

[caf]

I use the following formula every day to count the number of blank cells in column-G. The range in column-g varies every day and I am just curious if there's a way to edit the formula to make it use a dynamic range rather than editing it every single day?

=COUNTIF('Raw_Data_2011'!G2:G923,"")

 

[Ralajer]

Welcome to the board caf,

First create the dynamic range. Ctrl-F3 -> New -> Set Name to ex. "dynamicRange" -> set Refers To

=OFFSET($G$2,0,0,COUNTA($A2:$A65536),1)

Using column A in COUNTA as an example but it should be a column that contains data in all rows up to the desired end row.

Then either use your formula

=COUNTIF(dynamicRange,"")
or
=COUNTBLANK(dynamicRange)

 

[Aladin Akyurek]

I use the following formula every day to count the number of blank cells in column-G. The range in column-g varies every day and I am just curious if there's a way to edit the formula to make it use a dynamic range rather than editing it every single day?

=COUNTIF('Raw_Data_2011'!G2:G923,"")

Is the column supposed to house text values? If so:

=COUNTIF('Raw_Data_2011'!G2:INDEX('Raw_Data_2011'!G:G,MATCH(REPT("z",255),'Raw_Data_2011'!G:G)),"")

 

[T. Valko]

I use the following formula every day to count the number of blank cells in column-G. The range in column-g varies every day and I am just curious if there's a way to edit the formula to make it use a dynamic range rather than editing it every single day?

=COUNTIF('Raw_Data_2011'!G2:G923,"")

You could but one good thing about the COUNTIF function is that it will only calculate based on the used range.

For example...

=COUNTIF('Raw_Data_2011'!G:G,"")

You're referencing the ENTIRE column but if you only have data down to row 25 then Excel is smart enough to only evaluate the range as:

=COUNTIF('Raw_Data_2011'!G1:G25,"")

That way it calculates more efficiently by not having to evaluate a bunch of empty unused cells.

The best way to define a dynamic range range depends on several factors.

What type of data will the range contain? Is it text? Numbers? Could be both?

Is the data entered in a contiguous block (no empty cells within the data range)?

Are there any formulas in this range? Do any of the formulas return formula blanks ("") ?