Results 1 to 10 of 10

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

  1. #1
    Seeker ExcelHelpNeeded's Avatar
    Join Date
    Jul 2019
    Posts
    10
    Articles
    0
    Excel Version
    Excel 365

    Tracking numbers in a row & counting them in columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,676
    Articles
    0
    Excel Version
    Office 365 Subscription
    One way!

    In I5 copied across and down:

    =COUNTIFS($C5:$F5,">="&MIN(N$5:N$13),$C5:$F5,"<="&MAX(N$5:N$13))
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker ExcelHelpNeeded's Avatar
    Join Date
    Jul 2019
    Posts
    10
    Articles
    0
    Excel Version
    Excel 365
    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 AliGW; 2021-04-18 at 05:33 PM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Seeker ExcelHelpNeeded's Avatar
    Join Date
    Jul 2019
    Posts
    10
    Articles
    0
    Excel Version
    Excel 365
    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!
    Attached Files Attached Files

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,676
    Articles
    0
    Excel Version
    Office 365 Subscription
    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!
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,676
    Articles
    0
    Excel Version
    Office 365 Subscription
    Well, was that any use? Some feedback would be nice.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Seeker ExcelHelpNeeded's Avatar
    Join Date
    Jul 2019
    Posts
    10
    Articles
    0
    Excel Version
    Excel 365
    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.
    Attached Files Attached Files

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,676
    Articles
    0
    Excel Version
    Office 365 Subscription
    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))
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #9
    Seeker ExcelHelpNeeded's Avatar
    Join Date
    Jul 2019
    Posts
    10
    Articles
    0
    Excel Version
    Excel 365
    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!

  10. #10
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,676
    Articles
    0
    Excel Version
    Office 365 Subscription
    Glad to help.
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

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