Problem with Dynamic Range & COUNTA function

[rmward]

Hey all,

I am trying to get a count of non-numeric cells in a range, with a dynamically selected range based on the column header, which is placed through a macro by the user of the form. So essentially, the user selects the appropriate column, clicks a button, and the macro fills in the header for the column.

This all works fine, as long as I have the column header in place, but if I have not yet placed the column header, my formula is returning a value of 1, when I am expecting a value of #N/A (which I will take care of with an IFERROR function wrapped around the main formula once I figure this out).

The formula I am using is:

=COUNTA(non_num)-COUNT(non_num)

and "non_num" is a named range with the following formula applied to it:

=OFFSET('Sheet1'!B7,2,HLOOKUP("QTY_PER",'Sheet 1'!B7:BW8,2,FALSE)-2,1000,1)

The second row in the range of the HLOOKUP just contains numeric values for the columns (A = 1, B = 2, etc...)

I think the problem is that the COUNTA function is returning a value of 1, which I don't understand since, as far as I can tell, it should not have a valid range in which to count any cell values, since there is no "QTY_PER" for the HLOOKUP to find. I would expect that since the HLOOKUP returns #N/A, the whole formula should throw an error...

Can anyone point out what it is that I'm missing?

Cheers!

 

[pgc01]

I would expect that since the HLOOKUP returns #N/A, the whole formula should throw an error...

Hi

I don't see why you would expect that.
I think the help on these 2 functions is very clear, CountA() counts error values, Count() doesn't.

Please check and post back if you have doubts.

 

[rmward]

Hmmm I guess what I was trying to say is that, since the formula's that are providing the range for the COUNTA() function aren't actually providing a range, it would throw an error due to not having a valid range to count non-blanks in... I know that the COUNTA() function counts error values, I just didn't expect it to count anything without a valid range...

Anyway, I worked around it by doing an error check on the HLOOKUP() function, and have it working the way I want now...