COUNTIF formula with named range breaks when there are blank cells

[Fishboy]

Hi all,

I have an Excel 2010 workbook that has a number of columns as dynamic named ranges designed in such a way that the ranges expand as new data rows are added. This has been achieved using the following example RefersTo: codes

Name: Screening_Location
RefersTo: =Screening!$D$3:INDEX(Screening!$D:$D, MATCH("zzzz", Screening!$D:$D),1)

Name: Screening_Scheme
RefersTo: =Screening!$F$3:INDEX(Screening!$F:$F, MATCH("zzzz", Screening!$F:$F),1)

Name: Screening_Function
RefersTo: =Screening!$G$3:INDEX(Screening!$G:$G, MATCH("zzzz", Screening!$G:$G),1)

On another sheet within the workbook I have a number of tables which carry out COUNTIF or COUNTIFS functions based on the data in the dynamic named ranges. For example

=IFERROR(COUNTIFS([B]Screening_Location[/B],"Corby",[B]Screening_Scheme[/B],"Graduate",[B]Screening_Function[/B],"Procurement"),"0")

My problem is being caused by incomplete data on the main source sheet containing the named ranges. Basically, if there are any columns containing blank cells, the COUNTIF or COUNTIFS formulas just stop working. I assume this is because the way I have created the named ranges means the range stops at the first blank cell maybe? My temporary workaround has been to fill the empty cells with a period "." to fool Excel into seeing this as a value to make the formulas work, but this is obviously not an ideal solution.

Does anyone know if / what I am doing wrong, or know of an alternative way of making the dynamic ranges still function if there are blank cells included?

 

[XOR LX]

Hi.

I assume by "not working" you mean that they give an error?

This may be due to the ranges being of unequal dimension. Try defining all of your dynamic ranges using the same construction to determine the end range reference, i.e. define LastRow as:

=MATCH("zzzz", Screening!$D:$D)

after which your range definitions become:

=Screening!$D$3:INDEX(Screening!$D:$D,LastRow,1)

=Screening!$F$3:INDEX(Screening!$F:$F,LastRow,1)

=Screening!$G$3:INDEX(Screening!$G:$G,LastRow,1)

which will ensure that they are all of an equal dimension.

Of course, it's possible that using column D for this purpose is not guaranteed to be sufficient, since there may be non-blank entries in columns F or G which you want to include but which reside in rows beyond the last non-blank entry in column D. If this is the case, perhaps choosing column F or column G for the definition of LastRow will guarantee that all data is always encapsulated?

If not, and you cannot be sure which of those three columns will contain the last non-blank entry, you could define LastRow instead as:

=MAX(MATCH("zzzz",Screening!$D:$D),MATCH("zzzz",Screening!$F:$F),MATCH("zzzz",Screening!$G:$G))

It might be worth pointing out, however, that if the only functions to which you are intending to pass these ranges are COUNTIF(S), then there is no need to construct dynamic ranges, since - unlike e.g. SUMPRODUCT and other array formulas - these functions suffer no loss in performance when entire columns are passed as the reference. As such, you could, for example, define Screening_Location as simply:

=Screening!$D:$D

provided of course that there is nothing in cells D1 or D2 which you would not wish to be considered as part of your count.

Regards

 

[Fishboy]

Hi Xor, thanks again for sticking your head into one of my query posts.

For once I am actually certain that all of the named ranges are of the same length (technically speaking). The source data is basically a big table where column A is a candidate name, and all subsequent columns directly relate to the the candidate from the A column. This means that each column should therefore be of the same length.

I think my problem originally occurred as there were blank cells dotted throughout the data in every column, which Excel may have ended up interpreting as ends of ranges therefore making them different lengths.

Anyway, that is all irrelevant as I think you are on to a winner here with the COUNTIF(S) entire column thing. As fancy and complicated as I have made the rest of the spreadsheet, the actual data I am interested are all from simple counts. Out of interest, the only other things these named ranges are used for is data validation for subsequent cell drop-down lists. Defining the ranges as entire columns shouldn't break this feature should it?

Either way I will give it a whirl on a copy of my data and hopefully that will resolve the issue.

Cheers!

 

[XOR LX]

Hi Xor, thanks again for sticking your head into one of my query posts.

You're always welcome!

For once I am actually certain that all of the named ranges are of the same length (technically speaking). The source data is basically a big table where column A is a candidate name, and all subsequent columns directly relate to the the candidate from the A column. This means that each column should therefore be of the same length.

This seems to contradict the Defined Names in your original post, though, whose definitions all derive from the column which they reference, and not some other, fixed column (which, agreed, would be the normal - and logical - way to go about creating such dynamic ranges).

So your definition for Screening_Location, for example, is dependent on the position of the last non-blank cell in column D, yet your definition for Screening_Scheme is dependent on the position of the last non-blank cell in column F, etc. And is it not possible that the occurrence of the last non-blank cell in one of these three columns can be in a different row to that for the other two columns?

I think my problem originally occurred as there were blank cells dotted throughout the data in every column, which Excel may have ended up interpreting as ends of ranges therefore making them different lengths.

The construction you are using will ignore blanks "dotted throughout the data", since it looks only for the last non-blank cell in that range, irrespective of whether there are blanks earlier on in that range or not.

Out of interest, the only other things these named ranges are used for is data validation for subsequent cell drop-down lists. Defining the ranges as entire columns shouldn't break this feature should it?

In what sense are they used for the validation? For example, is your validation set as a "List" variety as simply e.g.:

=Screening_Location

with no further manipulation?

Regards

 

[Fishboy]

This seems to contradict the Defined Names in your original post, though, whose definitions all derive from the column which they reference, and not some other, fixed column (which, agreed, would be the normal - and logical - way to go about creating such dynamic ranges).

So your definition for Screening_Location, for example, is dependent on the position of the last non-blank cell in column D, yet your definition for Screening_Scheme is dependent on the position of the last non-blank cell in column F, etc. And is it not possible that the occurrence of the last non-blank cell in one of these three columns can be in a different row to that for the other two columns?

Basically, if you forgo the "Back To Top" macro button frozen in row 1, row 2 has column headers

	A	B	C	D	E	F
1	First Name	Surname	Location Choice 1	Location Choice 2	Scheme	Function Choice (Input) Discipline
2	Sayed	Shah	Shotton	N/A	Graduate	Electrical
3	Peju	Oyewole	Trostre	N/A	Graduate	Electrical
4	Ramraj	Natarajan	Hartlepool	N/A	Graduate	Electrical
5	Douglas	Law	Corby	N/A	Graduate	Electrical
6	Moneeb	Ashraf	Shotton	N/A	Graduate	Electrical
7	Zain	Imran	Stocksbridge	N/A	Graduate	Electrical
8	Brady	Morales	Corby	N/A	Graduate	Electrical
9	Pengfei	Gao	Corby	N/A	Graduate	Electrical

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

For every person in the list there *should* be a value in each subsequent column corresponding to that person. I made the ranges dynamic so that I could add more people to the end of the list as required, but it seems that is not necessary for what I need. I'm making things more complicated than they need to be I reckon.

The construction you are using will ignore blanks "dotted throughout the data", since it looks only for the last non-blank cell in that range, irrespective of whether there are blanks earlier on in that range or not.

Ahhh, that is good to know. I suspect that was originally why I sought out that method of named range in the first place a few months back.

In what sense are they used for the validation? For example, is your validation set as a "List" variety as simply e.g.:

=Screening_Location

with no further manipulation?

Pretty much this, although now I think about it, the lists are generated from a separate list on a hidden sheet with their own named ranges so this shouldn't be an issue either.

Having tried out your last suggestion of using entire columns for these named ranges, everything seems to be working fine. It just goes to show, sometimes simple is all that is required, no need for me to have tried to make it fancy and future-proof when this would have done the job anyway!

Thanks again mate

 

[XOR LX]

You're welcome again.

Just bear in mind that, although you can get away with it in functions such as COUNTIF(S), SUMIF(S), INDEX (in its standard use), etc., as they stand (i.e. as full column references), using those Defined Ranges in any array formula - and by that I include not just CSE formulas but also the likes of SUMPRODUCT - would not at all be a good idea.

With such functions it really is worthwhile creating dynamic, reduced ranges.

Cheers