Welcome Guest
You last visited April 19, 2019, 1:24 am
All times shown are
Eastern Time (GMT-5:00)

# Need some help with excel counting vertical

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

 Page 1 of 3
Krypton
United States
Member #140098
March 11, 2013
908 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

United States
Member #145203
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)
criteria is 8 in your case

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

Krypton
United States
Member #140098
March 11, 2013
908 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

United States
Member #145203
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

Krypton
United States
Member #140098
March 11, 2013
908 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

United States
Member #145203
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"

Economy class
Belgium
Member #123696
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.

Economy class
Belgium
Member #123696
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.

 2090 1 9 9 1 Bingo 1 F.Rc4Cc4: <7> 9 <5> 8 <3> 1 <1> 7 2089 9 9 8 1 Bingo 2 F.Rc4Cc4: <6> 8 9 <2> 7 <1> 1 6 2088 9 8 8 9 Bingo 3 F.Rc4Cc4: <6> 8 <5> 9 <2> 6 7 <1> 5 2087 8 8 7 9 Bingo 4 F.Rc4Cc4: <5> 8 <4> 9 <2> 5 6 7 <1> 4 2086 7 8 6 9 Bingo 5 F.Rc4Cc4: <4> 8 <3> 5 9 <2> 4 6 7 2085 6 8 5 9 Bingo 6 F.Rc4Cc4: <4> 5 <3> 4 8 <2> 6 7 9 2084 5 8 4 9 Bingo 7 F.Rc4Cc4: <5> 5 <4> 4 <2> 6 7 8 <1> 9 2083 5 7 4 8 Bingo 8 F.Rc4Cc4: <5> 5 <4> 4 <3> 6 <2> 7 <1> 3 8 2082 5 6 4 7 Bingo 9 F.Rc4Cc4: <5> 4 5 <3> 6 <2> 3 <1> 7 2081 5 5 4 6 Bingo 10 F.Rc4Cc4: <5> 4 5 <3> 3 <2> 6 <1> 2 2080 4 5 3 6 Bingo 11 F.Rc4Cc4: <5> 4 <4> 5 <3> 3 <2> 2 <1> 1 6 2079 4 4 3 5 Bingo 12 F.Rc4Cc4: <5> 4 <4> 5 <2> 1 2 3 <1> 9 2078 3 4 2 5 Bingo 13 F.Rc4Cc4: <4> 4 5 <2> 1 2 9 <1> 3 8 2077 2 4 1 5 Bingo 14 F.Rc4Cc4: <4> 4 <3> 5 9 <2> 1 8 <1> 2 3 2076 1 4 9 5 Bingo 15 F.Rc4Cc4: <4> 9 <3> 4 8 <2> 3 5 <1> 1 2 2075 9 4 8 5 Bingo 16 F.Rc4Cc4: <4> 8 <3> 3 9 <2> 2 4 <1> 5 7 2074 9 3 8 4 Bingo 17 F.Rc4Cc4: <4> 8 <3> 2 3 <2> 7 9 <1> 1 4 2073 9 2 8 3 Bingo 18 F.Rc4Cc4: <4> 8 <3> 2 7 <2> 1 3 9 2072 8 2 7 3 Bingo 19 F.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.

Economy class
Belgium
Member #123696
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:

 SERIES SIZE: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,1 1:26 2:6 3:1
Krypton
United States
Member #140098
March 11, 2013
908 Posts
Offline
 Posted: April 6, 2014, 7:36 am - IP Logged

Patron,

[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

Economy class
Belgium
Member #123696
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.

COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6459 Posts
Offline
 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/10 5 9 4 P1 0 1 2 3 4 5 6 7 8 9 02/12/10 5 3 7 0 2 0 0 0 0 1 0 0 0 1 02/11/10 5 4 5 1 0 0 0 0 0 0 0 0 0 0 02/10/10 0 9 8 2 1 0 2 0 0 0 0 0 0 0 02/09/10 0 3 6 3 0 0 0 0 0 0 0 0 0 0 02/08/10 0 6 5 4 0 0 0 0 0 0 0 0 0 0 02/07/10 2 0 9 5 0 0 0 0 0 2 0 0 0 0 02/06/10 2 6 7 6 1 0 0 0 0 0 0 0 0 0 02/05/10 2 4 4 7 0 0 0 0 0 0 0 0 0 0 02/04/10 9 9 4 8 0 0 0 0 0 0 0 0 0 0 02/03/10 0 0 3 9 0 0 1 0 0 0 0 0 0 0 02/02/10 6 3 6
Economy class
Belgium
Member #123696
February 27, 2012
4035 Posts
Offline
 Posted: April 6, 2014, 8:30 am - IP Logged
 2/13/2010 5 9 4 5*3 9*1 4*1 2/12/2010 5 3 7 3*1 7*1 2/11/2010 5 4 5 4*1 5*1 2/10/2010 0 9 8 0*3 9*1 8*1 2/09/2010 0 3 6 3*1 6*1 2/08/2010 0 6 5 6*1 5*1 2/07/2010 2 0 9 2*3 0*1 9*1 2/06/2010 2 6 7 6*1 7*1 2/05/2010 2 4 4 4*1 4*2 2/04/2010 9 9 4 9*1 9*1 2/03/2010 0 0 3 0*1 0*1 3*1 2/02/2010 6 3 6 6*1 3*1 6*1

Coded

COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6459 Posts
Offline
 Posted: April 6, 2014, 9:21 am - IP Logged
Krypton
United States
Member #140098
March 11, 2013
908 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

 Page 1 of 3