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

Leon98584

New Member
Joined
Jun 18, 2015
Messages
3
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

AlexandraT

Board Regular
Joined
Mar 23, 2015
Messages
144
There are functions that look in a cell to see if it's a number or formula: ISNUMBER() and ISFORMULA(). Would these help?
 

Leon98584

New Member
Joined
Jun 18, 2015
Messages
3
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

Board Regular
Joined
Mar 23, 2015
Messages
144
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

New Member
Joined
Jun 18, 2015
Messages
3
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,148
Messages
5,835,681
Members
430,375
Latest member
datdog22

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top