In column A I have a formula that collates two other columns together.

=IF(ISBLANK(B5),"", B5&" "&C5)

Then in the Name Manager, a dynamic named range to use for a data validation drop down list.


However this is still counting all cells with the formula in, even if they are blank. I tried using COUNTIF to count blank cells.

=OFFSET(Projects!$A$5,0,0,COUNTIF(Projects!$A:$A, "<>"),1)

But this still including every cell with a formula in, even if it is "blank".

Is there a way to get the named range to ignore formulas that return blank?


Below is an example of how the data in the first bit looks. (starts at row 5 so kept it true to the example)

               A                      B          C
5 =IF(ISBLANK(B5),"", B5&" "&C5)   Director     123
6 =IF(ISBLANK(B6),"", B6&" "&C6)   Officer      321
7 =IF(ISBLANK(B7),"", B7&" "&C7)

Continue down to row 1000. Column A is hidden, and users only amend the data in B and C. Values are often added, or removed, so the number of entries in B and C change constantly

Then for my DNR I am trying to use in a different sheet I set up a Named Range called JobTitle using the following.

=OFFSET(Projects!$A$5,0,0,COUNTIF(Projects!$A5:$A1000, "<>"),1)

My current work around is to count the non-blank cells in column B

=OFFSET(Projects!$A$5,0,0,COUNTIF(Projects!$B5:$B1000, "<>"),1)

Which is currently working, however for future reference I would like to know how to create the DNR from column A.

  • As the formula in column A is dependent on B having data, I have changed the DNR to count if column B is not blank. =OFFSET(Projects!$A$5,0,0,COUNTIF(Projects!$B5:$B1000, "<>"),1) which is a suitable work around. But would still like to know how count unsatisfied formulas as blank. Commented Apr 6, 2016 at 12:39
  • I am not following what you are trying to accomplish. Are you trying to return the values of the formulas if there is a result, otherwise leave it blank? Why are you using OFFSET?
    – CharlieRB
    Commented Apr 6, 2016 at 12:47
  • It is for use in the Name Manager to be used for a data validation drop down list elsewhere. Commented Apr 6, 2016 at 12:56
  • Don't try to think of the whole process at one time. First things first...you are trying to return only the results of the formulas in column A, correct? Why are you using OFFSET?
    – CharlieRB
    Commented Apr 6, 2016 at 13:18
  • Correct, the whole column A is formulas, and I only want the results that you can "see". I am using OFFSET because that is how to create a DNR, excel-easy.com/examples/dynamic-named-range.html Commented Apr 6, 2016 at 13:27

2 Answers 2


I would define JobTitle rather as:


which, by employing INDEX in place of OFFSET, lessens the volatility of the construction.

Note that the COUNTIF portion rests on the assumption that the values in the range Projects!$A5:$A$1000 are text, not numeric. Given that each of the values within this range is derived via a string concatenation, however, I would imagine that this assumption is a fair one.


  • What if I need to use this for a dynamic range of data? Commented Mar 28 at 19:31

Instead of

The COUNTA function counts the number of cells that are not empty in a range.

A cell containing a formula, even if it returns "" isn't empty.

  • That didn't work for me, but this does,taken from the previous answer: =COUNTIF(Projects!$A:$A,"?*")
    – JohnP2
    Commented May 15, 2020 at 20:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .