Use of 3 columns to determine the count of completed items in an array

[Leon98584]

Hello,

I have an array of items which contains a Mile post in Column A, segment number in Column B, and offset number in Column C. I am attempting to determine the number of completed items based on whether the cells in Columns B and C have valid numbers. I am currently using Countifs, but this does not provide the accuracy needed as I cannot find a way to test whether a cell in the column, in countifs, "isnumber". My current formula "=countifs(A,"<8",B,">=0",C,">=0")" though is fairly close. The problem is that each cell in all three columns actually contains formulas that obtains the actual values from another part of the worksheet, so the cells value content is either "" or some number starting with 0. (BTW, on rare occasions that numbers can be negative, but in most cases the numbers are positive.
I am also using, in the same worksheet the "get.cell" function to determine if a cell contains a formula or not, but could find nothing in that macro that could be used to to determine if the cell in a column is a number.
Any help in answering this question would be appreciated

Thanks Loads
Leon98584

 

[AlexandraT]

There are functions that look in a cell to see if it's a number or formula: ISNUMBER() and ISFORMULA(). Would these help?

 

[Leon98584]

I did try these functions, but they do not work with countifs. I was hoping to find a macro such as the one for determining what cells contains formulas in a range (get.cell). But this particular function does not contain a parameter for determining if a cell contains a number (isnumber, isna, isformula). The only thing that I could find that would work with Countifs is simple comparisons, and then only if they were included as a string?
I know for conditional formating you can use the formula =isnumber($a4), and it will work as conditional formatting seems to have a built in iteration. But this does not work with countifs(a,">8",b,"=isnumber($b1)",c,"=isnumber($c1)"). This always came up with 0 as a result, even though there were numbers in both columns.

Thanks
Leon98584

 

[AlexandraT]

You could add a helper column (let's say column D) to check if both B and C are numbers and apply it to the entire column: =IF(AND(ISNUMBER(B),ISNUMBER(C)),1,0) and then count A if there is a 1 in D :=COUNTIFS(A,">8",D,1). Or just sum up column D. If the column gets in the way visually, you can hide it.

Alex

 

[Leon98584]

Thanks, That did work, but didn't have to use it as a "IF" statment. Just using And(isnumber(b1),isnumber(c1)) puts a flag in column T. Then my countifs(a,"<=8",T,True) gives me the required accuracy

Thanks Loads!