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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,652
Members
414,083
Latest member
Mrsash

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