# Countif function for array of numbers

Assuming an array of numbers 4 rows, 5 columns:

3 5 1 6 5   1
2 4 9 2 4   2
2 2 6 0 1   2
1 5 2 3 1   0
2 6 3 1 2   0

I would like to tally in the 6th column (underlined) the 1st time any value equal to or less than 2 appears in each of the previous 5 columns.  I've entered the result I'm looking for.  Essentially, once any value equal to or less than 2 appears in a column, that column is done for purposes of the tally.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
There ought to be a more straightforward way to do this, but here is a suggestion:

Create another 5 columns to the right of the others
In the first row, put a 0 for every instance of 2 or less, 1 otherwise (e.g. 1 1 0 1 1 for the first row)
In all other rows, put a 0 in every column where there is a 0 above, a 0 in a column that has <=2 in the data, and 1 otherwise.
Second row would end up with 0 1 0 0 1.

If that wasn't clear enough, let me know.
EngineerCommented:
The first three rows make sense as per your explanation. But I do not understand how you get the last two rows. Please explain.
Commented:
Second row is 01001
Third row:
(not the efficient programmatic method, but trying to explain)
Bring the 0s down: 0 x 0 0 x
Your data is 2 2 6 0 1
You only need test the second and fifth digits (where there is a 1 in the new data (01001) in row 2)
The second digit is a 2, so that puts a 0 in the new column 2; the fifth digit is a 1, so that puts a 0 in the fifth column.

My initial description was wrong!
To get the answer (count), check only the digits with a 1 in the column above (not the same column).
That is, when looking at the third row (2 2 6 0 1) you test only the digits that match with 0 1 0 0 1.  That is, only test the second and fifth digits (same as with making the new mask; combine the calculation for efficiency).  Both are 2 or less (as seen above), so the answer is 2.

To help, let's do the next row: 1 5 2 3 1.
Well.. this isn't very instructive as the mask is all 0s, so you know the answer will be 0!

Let's imagine that this is the third row.  You will compare it to the mask from the second row, which is 0 1 0 0 1.  The second digit (5) is greater than 2 so that brings the 1 down and doesn't add to the "score" for this row.  The fifth digit (1) is less than or equal to 2, so that puts a 0 in the mask and adds to the "score" for this row.  The new mask would be 0 1 0 0 0 and the "score" for the row would be 1.
Author Commented:
Saqib:  There appears no new instance of an number <=2 in the array of numbers.  Once a value <=2 appears in a column, subsequent instances aren't tallied in column 6.  Column 6 tallies across the rows.  The tally is a 2 step process.

CompProbSolv:  I can make that work, but it's a bit cumbersome.  I'm working with an array that is 25 rows by 1000 columns.
Commented:
I can see that this would make a lot of "if" statements.  We can work around it with a third set of columns

Rather than explain, I made the attached spreadsheet.  It should be clear and can easily be extended to 25 columns by copying.  It does make the spreadsheet three times as wide, though.
count.xlsx

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

EngineerCommented:
I am sorry I am lost in this explanation. Is there a website which explains this process in detail? Till then I shall let CompProbSolv have a go at it.
Author Commented:
I tweaked your solution a bit for my purposes, but you sent me in the right direction with the "1"s and "0"s.  Thanks!
Commented:
This could be done much more efficiently with VBA code.  It is a good example of where you have to weigh development time vs. execution time and "elegance".  Sometimes, you just want to get the job done!
EngineerCommented:
I have reduced CompProbSolv's solution to one set of helper columns
count.xlsx
Commented:
I must expose my ignorance here as I've never used a range in an IF statement.  This is a much slicker solution!
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.