Tracking numbers in a row & counting them in columns

ExcelHelpNeeded

New member
Joined
Jul 21, 2019
Messages
10
Reaction score
0
Points
0
Excel Version(s)
Excel 365
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.

Thanks in advance for your help.
 

Attachments

  • TRACKING NUMBERS.xlsx
    15.2 KB · Views: 5
One way!

In I5 copied across and down:

=COUNTIFS($C5:$F5,">="&MIN(N$5:N$13),$C5:$F5,"<="&MAX(N$5:N$13))
 
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!
 
Last edited by a moderator:
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!
 

Attachments

  • TRACKING NUMBERS.xlsx
    17.2 KB · Views: 6
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!
 
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. :faint:
 

Attachments

  • TRACKING NUMBERS.xlsx
    21 KB · Views: 11
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))
 
Dog gone it! Are you serious? I cannot believe that I missed that. :redface: Thank you again so much Ali! This has all been very helpful!
 
Back
Top