# Thread: Tracking numbers in a row & counting them in columns

1. ## Tracking numbers in a row & counting them in columns

I am entering 4 numbers in a row. I want the columns to count how many times a specific number comes up in that row of 4 numbers. It is hard to explain so there are more details in the attached spreadsheet.

I cannot even begin to think how to solve this.

2. One way!

In I5 copied across and down:

=COUNTIFS(\$C5:\$F5,">="&MIN(N\$5:N\$13),\$C5:\$F5,"<="&MAX(N\$5:N\$13))

3. That is brilliant! I was making this soooo much more complicated. I have never used or even seen the &MIN and &MAX though. Can you explain how those work? Thanks Ali!

4. Ali what you did was perfect and brilliant and I have a use for those numbers in that order.

How would the formula be different if my numbers were different and not numerical like in my last example? I also have a need for a matrix like this as well. I thought one formula would translate to the next matrix, but that does not appear to be the case.

I have been looking up information on the MIN / MAX and the &, but those examples are usually just picking out how many times a color comes up in a column and how many times that color comes up. All of the examples I saw for the & had to do with joining a first and last name in two different cells.

So I could not quite figure out your last formula. I still think it is brilliant because I have never seen one written like that before.

I have attached the same spreadsheet, but with more random numbers in the matrix this time. Thanks again so much for your help!

5. Try this one in I5 copied across and down:

=SUMPRODUCT(COUNTIF(\$H5:\$K5,S\$5:S\$13))

I have never used or even seen the &MIN and &MAX though. Can you explain how those work?
=MAX(range) simply finds the maximum number in the range!

6. Well, was that any use? Some feedback would be nice.

7. Yes it was helpful and thank you very much Ali!

Sorry, but I got stuck on the formula last night. I tried using the same formula to track a second set of numbers, but it is populating the spreadsheet with numbers from somewhere and I cannot figure out why it is doing that and where those numbers are coming from. It obviously throws off my counts.
I was going to use that formula to search several number matrices, but I just cannot see the error in my second formula. I have attached the spreadsheet again with the problem that I am having.

8. You forgot to fix the final range. You have this:

=SUMPRODUCT(COUNTIF(\$K5:\$N5,B\$17:B26))

But you need this in V5:

=SUMPRODUCT(COUNTIF(\$K5:\$N5,B\$17:B\$26))

9. Dog gone it! Are you serious? I cannot believe that I missed that. Thank you again so much Ali! This has all been very helpful!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•