+ Reply to Thread
Results 1 to 10 of 10

Count the total number of times several numbers appear on each row

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Count the total number of times several numbers appear on each row

    I need a formula that will count the total number of times several numbers appear on each row of worksheet and display the result on each row. For example, in the table below, the numbers 10, 43, and 85 show up 3 times in row A2-I2 and 2 times in A3-I3. These totals are displayed in cells J2 and J3 respectively.

    A2 B2 C2 D2 E2 F2 G2 H2 I2 J2
    -- -- -- -- -- -- -- -- -- -- --
    01 10 43 44 57 66 72 85 99 03

    A3 B3 C3 D3 E3 F3 G3 H3 I3 J3
    -- -- -- -- -- -- -- -- -- -- --
    08 12 27 32 43 57 69 71 85 02

    I've searched all over and have tried many different variations of "COUNTIF", "COUNTIFS" but I can't figure it out. I've also tried these functions putting the numbers to count as arrays (using control-shift-enter) but no success.

    The worksheet contains thousands of numbers (formatted as numbers) on hundreds of rows. The numbers to search for in each row will always be 10, 43, and 85. The numbers on each row range from 01-99 and never repeat on the same row.

    Appreciate your help.
    Last edited by dwontheweb; 11-27-2012 at 07:01 PM. Reason: formattting

  2. #2
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Count the total number of times several numbers appear on each row

    not the prettiest thing in the world, but:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Count the total number of times several numbers appear on each row

    Use this:

    =SUM(COUNTIF(A2:J3,{10,43,85})) * Enter normally - this is a non-array formula

    - Moo

    * Oops, didn't know you wanted it per line, I thought you just wanted the total number of times all 3 are present (which would be 5). To apply it at the end of each line use this:

    =SUM(COUNTIF(A2:I2,{10,43,85}))

    Then fill that down column J as needed.

    - Moo
    Last edited by Moo the Dog; 11-28-2012 at 12:07 AM. Reason: Formulas are regular, not array. Apply with Enter.

  4. #4
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Count the total number of times several numbers appear on each row

    Is this what you are looking for?
    Please click the * icon below if I have helped.

  5. #5
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Count the total number of times several numbers appear on each row

    ** ADDED THIS TO MY PREVIOUS POST **

    Oops, didn't know you wanted it per line, I thought you just wanted the total number of times all 3 are present (which would be 5). To apply it at the end of each line use this:

    =SUM(COUNTIF(A2:I2,{10,43,85}))

    Then fill that down column J as needed.

    - Moo
    Last edited by Moo the Dog; 11-28-2012 at 12:08 AM.

  6. #6
    Registered User
    Join Date
    11-27-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Count the total number of times several numbers appear on each row

    This worked great! Thanks Moo and to the rest of you as well!

    =SUM(COUNTIF(A2:J3,{10,43,85})) and enter with Ctrl + Shift + Enter

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Count the total number of times several numbers appear on each row

    So you did want it totaled all in one cell as my first formula did. Glad it works for ya, and glad to help.

    - Moo

    - - - - - - - - - -
    If you are satisfied with the solution(s) provided, please mark the thread as [SOLVED] by clicking on the words 'Thread Tools' above your 1st post in this thread. Then select 'Mark Thread as Solved'.

    Also, a good way to 'Say thanks' to those who have helped is to click on the star below their name in one of their posts. It's always appreciated.

  8. #8
    Registered User
    Join Date
    11-27-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Count the total number of times several numbers appear on each row

    Moo, this is the one that I needed:
    =SUM(COUNTIF(A2:I2,{10,43,85}))

    But may use the other one later for another function. Thanks again!

  9. #9
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Count the total number of times several numbers appear on each row

    Ahh, got it. You're welcome.
    - Moo

  10. #10
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Count the total number of times several numbers appear on each row

    * Please see my post change above noting that both formulas I provided are regular formulas (applied with Enter) and not array formulas. It didn't change the outcome, just not necessary to use Ctrl + Shift + Enter.

    I was working on a few different formulas at the time and got confused between them.

    Thanks to icestationzbra for pointing it out to me.

    - Moo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1