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

#### Leon98584

##### New Member
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

Leon98584

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
There are functions that look in a cell to see if it's a number or formula: ISNUMBER() and ISFORMULA(). Would these help?

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

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

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

Replies
2
Views
257
Replies
1
Views
292
Replies
16
Views
634
Legacy 143009
L
Replies
2
Views
390
Replies
4
Views
649

1,211,744
Messages
6,103,670
Members
447,876
Latest member
rodqntr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back