Welcome Guest
Log In | Register )
You last visited January 19, 2017, 9:52 am
All times shown are
Eastern Time (GMT-5:00)

Need some help with excel counting vertical

Topic closed. 32 replies. Last post 3 years ago by SergeM.

Page 1 of 3
PrintE-mailLink
Avatar
Krypton
United States
Member #140102
March 11, 2013
904 Posts
Offline
Posted: April 4, 2014, 8:21 pm - IP Logged

I am looking for a guru in excel to help me count numbers vertically and give me a total out to the side. For example

 

8

8    = 4  There are 4 8's

8

8

 

https://www.dropbox.com/s/iv66e9j3wfszn08/Count%20Vertical.xlsx

    basilio's avatar - tools

    United States
    Member #145207
    July 31, 2013
    94 Posts
    Offline
    Posted: April 4, 2014, 9:49 pm - IP Logged

    I am looking for a guru in excel to help me count numbers vertically and give me a total out to the side. For example

     

    8

    8    = 4  There are 4 8's

    8

    8

     

    https://www.dropbox.com/s/iv66e9j3wfszn08/Count%20Vertical.xlsx

    excel count does not care if the range is vertical or horizontal

    COUNTIF(range,criteria)
    range is your range
    criteria is 8 in your case

    example
    COUNTIF(A1:A10,8) or COUNTIF(A1:F1,8)

      Avatar
      Krypton
      United States
      Member #140102
      March 11, 2013
      904 Posts
      Offline
      Posted: April 4, 2014, 10:02 pm - IP Logged

      for the excel challenged folks like me but trying to learn.  how do i set this up?  Where would I place the COUNTIF(A1:A10,8)?  I am not sure if you looked at my file in dropbox but I will have well over 1000 rows of numbers to count  Where would I place the countif and where do I place my actual file (numbers to be counted)  Thanks for your help

        basilio's avatar - tools

        United States
        Member #145207
        July 31, 2013
        94 Posts
        Offline
        Posted: April 4, 2014, 10:14 pm - IP Logged

        sorry i did not look at your file, but i just did

        what your looking for is if you have a double or triples ( you don’t care what the numbers are ) right ?

         

        if so just replace cell I4 with the following "=SUM(IF(FREQUENCY(C4:F4,C4:F4)>0,1))" then you can drag it down

        this returns how many unique numbers you have, so 3 means you have 1 double, 2 you have a triple

         

        hope this help

          Avatar
          Krypton
          United States
          Member #140102
          March 11, 2013
          904 Posts
          Offline
          Posted: April 4, 2014, 10:37 pm - IP Logged

          All I am tryig to do is count how many times a specific number appears in a row and get the total count out to the side for example as in my attachment and below

           

          2

          2    =4x

          2

          2

          1

          1    =2x

          5

          5

          5     =6x

          5

          5

          5

           

          and so on

            basilio's avatar - tools

            United States
            Member #145207
            July 31, 2013
            94 Posts
            Offline
            Posted: April 4, 2014, 11:23 pm - IP Logged

            Hope this help,

            how many 2s
            =COUNTIF(A2:A13,2)&"x2"

            how many 1s
            =COUNTIF(A2:A13,1)&"x1"

            how many 5s
            =COUNTIF(A2:A13,5)&"x5"
             

              SergeM's avatar - slow icon.png
              Economy class
              Belgium
              Member #123700
              February 27, 2012
              4035 Posts
              Offline
              Posted: April 5, 2014, 4:23 am - IP Logged

              You are not looking for the kind of display like 4x2.

                SergeM's avatar - slow icon.png
                Economy class
                Belgium
                Member #123700
                February 27, 2012
                4035 Posts
                Offline
                Posted: April 5, 2014, 6:08 am - IP Logged

                You used four rows and four columns. You wanted to order the frequency descending.

                20901991Bingo 1F.Rc4Cc4: <7> 9 <5> 8 <3> 1 <1> 7
                20899981Bingo 2F.Rc4Cc4: <6> 8 9 <2> 7 <1> 1 6
                20889889Bingo 3F.Rc4Cc4: <6> 8 <5> 9 <2> 6 7 <1> 5
                20878879Bingo 4F.Rc4Cc4: <5> 8 <4> 9 <2> 5 6 7 <1> 4
                20867869Bingo 5F.Rc4Cc4: <4> 8 <3> 5 9 <2> 4 6 7
                20856859Bingo 6F.Rc4Cc4: <4> 5 <3> 4 8 <2> 6 7 9
                20845849Bingo 7F.Rc4Cc4: <5> 5 <4> 4 <2> 6 7 8 <1> 9
                20835748Bingo 8F.Rc4Cc4: <5> 5 <4> 4 <3> 6 <2> 7 <1> 3 8
                20825647Bingo 9F.Rc4Cc4: <5> 4 5 <3> 6 <2> 3 <1> 7
                20815546Bingo 10F.Rc4Cc4: <5> 4 5 <3> 3 <2> 6 <1> 2
                20804536Bingo 11F.Rc4Cc4: <5> 4 <4> 5 <3> 3 <2> 2 <1> 1 6
                20794435Bingo 12F.Rc4Cc4: <5> 4 <4> 5 <2> 1 2 3 <1> 9
                20783425Bingo 13F.Rc4Cc4: <4> 4 5 <2> 1 2 9 <1> 3 8
                20772415Bingo 14F.Rc4Cc4: <4> 4 <3> 5 9 <2> 1 8 <1> 2 3
                20761495Bingo 15F.Rc4Cc4: <4> 9 <3> 4 8 <2> 3 5 <1> 1 2
                20759485Bingo 16F.Rc4Cc4: <4> 8 <3> 3 9 <2> 2 4 <1> 5 7
                20749384Bingo 17F.Rc4Cc4: <4> 8 <3> 2 3 <2> 7 9 <1> 1 4
                20739283Bingo 18F.Rc4Cc4: <4> 8 <3> 2 7 <2> 1 3 9
                20728273Bingo 19F.Rc4Cc4: <5> 8 <4> 7 <2> 1 2 9 <1> 3

                F: frequency
                Rc: rows count
                Cc: columns count
                <4>: four times (no closing tag </4> here)

                Note that there is a function in Excel that returns the first found most frequent number of a range. You might use percentiles too. Nesting with & allows you to display more data in one cell. Generally you don't want to do that because you would have to split the cell data again. For reading with they eyes this isn't necessary.

                  SergeM's avatar - slow icon.png
                  Economy class
                  Belgium
                  Member #123700
                  February 27, 2012
                  4035 Posts
                  Offline
                  Posted: April 5, 2014, 6:41 am - IP Logged

                  I have that you are looking for lengths of series, not frequencies.

                  You can do it!

                  Example of a summary for one number:

                  SERIESSIZE:COUNT.SERIES
                  2,1,1,1,1,1,1,1,1,2,1,1,1,1,2,2,1,1,2,1,2,1,1,3,1,1,1,1,1,1,1,1,11:26 2:6 3:1
                    Avatar
                    Krypton
                    United States
                    Member #140102
                    March 11, 2013
                    904 Posts
                    Offline
                    Posted: April 6, 2014, 7:36 am - IP Logged

                    Patron,

                    If you look at the spreadsheet that I uploaded to dropbox

                    [url]https://www.dropbox.com/s/iv66e9j3wfszn08/Count%20Vertical.xlsx[/url]

                    you will see the following:

                    Game 2089 and 2088 have two 8's or 9's (pick either one as it does not matter) there will always be two sets of digits behind each other. I only want to capture one set of them and it does NOT matter which one

                    Anyways…..Game 2089 and 2088 I have two 8's. I want to write a 2 say next to Bingo 2 a couple columns over to the right
                    Then you see five 9's. I want to place a 5 next to Bingo 3
                    Then I have four 4's, I want to place a 4 next to Bingo 7
                    Next two 6's place a 2 next to Bingo 10
                    Next two 3's …place 2 next to Bingo 11
                    Next five 5's….place a 5 next to Bingo12
                    Next three 8's …place a 3 next to bingo 16

                    And so on and so on. I do not need to capture the 1's like the 7 in games 2087, 2086 ( 7or 6) and 2083 (7 or 8) so do not worry about the single digits that are by themselves

                    The ones with C1-C2-C3-C4 are done the same way except there are no single digits. I have numbers next to all the Bingo's and if you count how many numbers there are vertically you will see they add up to the digit off to the right. Like on the bottom games 2089 has a 2 for two 7's and game 2084 has a 4 for four 1's, Game 2081 has a 8 a long side Bingo 10 for eight 3's

                    I hope this makes better sense.
                    Plain and simple counting. No crunching just counting digits

                      SergeM's avatar - slow icon.png
                      Economy class
                      Belgium
                      Member #123700
                      February 27, 2012
                      4035 Posts
                      Offline
                      Posted: April 6, 2014, 7:57 am - IP Logged

                      First thing is that you need to step a bit of the "I want this just like that!". Next thing is that you have to build little one row matrices that do little things. You get the information you are looking for out of the columns there. You continue building. Finally you get a result and you show or hide the steps to get there by hiding the columns or not. - The one cell solution is only possible by programming on the backside or you are a wonderboy with matrix functions. - What I showed above is just an extirp of one of my charts that I made around three years ago. - So get your hands dirty, there ain't no free lunch.

                        CARBOB's avatar - FL LOTTERY_LOGO.png
                        ORLANDO, FLORIDA
                        United States
                        Member #4924
                        June 3, 2004
                        5966 Posts
                        Online
                        Posted: April 6, 2014, 8:17 am - IP Logged

                        This formula =SUMPRODUCT(--($B$2:$B$12=F$2),(--($B$3:$B$13=$E3))) goes in the second row,secon column. This is position 1 only, you have to create the other 2 positions.

                        I am no guru.

                         

                        02/13/10594P10123456789
                        02/12/1053702000010001
                        02/11/1054510000000000
                        02/10/1009821020000000
                        02/09/1003630000000000
                        02/08/1006540000000000
                        02/07/1020950000020000
                        02/06/1026761000000000
                        02/05/1024470000000000
                        02/04/1099480000000000
                        02/03/1000390010000000
                        02/02/10636                     
                          SergeM's avatar - slow icon.png
                          Economy class
                          Belgium
                          Member #123700
                          February 27, 2012
                          4035 Posts
                          Offline
                          Posted: April 6, 2014, 8:30 am - IP Logged
                          2/13/20105945*39*14*1
                          2/12/2010537  3*17*1
                          2/11/2010545  4*15*1
                          2/10/20100980*39*18*1
                          2/09/2010036  3*16*1
                          2/08/2010065  6*15*1
                          2/07/20102092*30*19*1
                          2/06/2010267  6*17*1
                          2/05/2010244  4*14*2
                          2/04/20109949*19*1 
                          2/03/20100030*10*13*1
                          2/02/20106366*13*16*1

                             Coded

                            CARBOB's avatar - FL LOTTERY_LOGO.png
                            ORLANDO, FLORIDA
                            United States
                            Member #4924
                            June 3, 2004
                            5966 Posts
                            Online
                            Posted: April 6, 2014, 9:21 am - IP Logged
                              Avatar
                              Krypton
                              United States
                              Member #140102
                              March 11, 2013
                              904 Posts
                              Offline
                              Posted: April 6, 2014, 10:23 am - IP Logged

                              Thanks Carbob

                               

                              om on my iPad at the moment but as soon as I get home I can't wait yo try this. It will save me a tremendous amount of time