# Consecutive Number Counting Formula

Oct 9, 2009
In cells v43:v52 I have data which will change often the data is always either a 1,-1 or a 0, I need a way to count from the bottom up and ignore anything that is not part of the chain. It can also contain 0 in rows that are not populated yet but the 0s will always be on the bottom and never in the middle or top.

I.E. in 3 weeks it could be

1

1

1

0

0

0

0

0

0

0

0

0

I would want the formula to spit out 3 because it was 1 3 times in a row..

Now say in week 5 it is

1

1

1..............................

View 7 Replies
ADVERTISEMENT
Nov 26, 2012

I have excel sheets with 71936 rows of data collected ~30 times a second for 40 minutes during and driving simulator drive (for 51 participants! all in separate worksheets). The output I get from the simulator includes speed, lane position etc. I've been averaging the data out into minute blocks for most variables, but what I really need to know is: if they exit the "lane" for more than 3 seconds, and if so, how many times they exit the lane in each minute.

Column A is the minute, column B is their lane position. Lane position ranges from -540 to 540. The middle of the left hand lane is -180 (the left hand lane ranges from -360 to 0 - we drive on the left in Australia). If they exit the left hand side of the lane, the lane position value will be between -360 and-540, and if they exit the right hand side the lane position value is 0 or greater.

A formula in a new column that can give a "1" each time they exit the lane for more than 3 seconds would be great, then I think I can make it sum how many times they do it in each minute.

View 6 Replies
View Related
Sep 18, 2006

I have read every single question on counting consecutive numbers in this forum but could not find what I need. I am trying to count the number of consecutive zeros in a row till the cell >0 then the counter is reset and restarts. One point to pay attention is that the number that is>0 when counting starts is included. I wrote a macro for it but somehow I am having problems with it. It returns 0. I want it to be for example

In the excel sheet

Numbers 160 0 0 400 0 0 200 0

Counts 3 3 2

Sub value_count()

Dim count As Integer

Dim k As Integer

Dim j As Integer

For k = 1 To 10

If Cells(1, k).Value > 0 Then

j = k

Do Until Cells(1, j).Value <> Cells(1, j + 1).Value

count = count + 1

j = j + 1

Loop

Cells(2, j).Value = count

If count >= 1 Then

k = k + count

End If

count = 0

End If

Next k

End Sub

View 6 Replies
View Related
Sep 28, 2012

I need a formula that will count the number of consecutive 3 0's from the following Data series. There are 22 such events.

0

6

15

[Code].....

View 9 Replies
View Related
Dec 11, 2013

I was trying to see the values only if the same value arrived more than three times in the given range.

Ex:

ABCDEFGHIJ

1AAAABAA

2

in the given range A1:J1, I would like to count 'A' only if it repeats more than or equal to 3 times. For row 1 I should get count as 4.

View 1 Replies
View Related
Jul 18, 2014

I have been trying to adapt a couple of the posts I've seen on this topic but they seem to refer to counting in rows and I'm having trouble translating that to columns.

I have a simple situation. I have 1s or 0s in columns from left to right. I only want to count the first occurrence of consecutive "1"s and only if the series starts with "1" in the left most column. (I am trying to calculate consecutive years of giving and the columns are FY14, FY13, FY12, etc. Only people who gave in FY14 would count.)

Example:

Row 2: 11101010

Row 3: 11001011

Row 4: 00100110

I would like Row 2 to count "3", Row 3 to count "2" and Row 4 to count "0".

View 2 Replies
View Related
Nov 12, 2009

My goal is to look at a column of numbers and count the number of consecutive cells that are positive (or negative), while ignoring blank cells that may be in between them. Using the first formula on this thread. http://www.excelforum.com/excel-gene...-than-0-a.html

I was able to adapt it to my own data, but whenever I had blank cells in my data the results were not reliable. the formula i am using now is =IF(COUNT(1/((A1:A27<0)*(A2:A28<0))),MAX(FREQUENCY(IF((A1:A27<0)*(A2:A28<0),ROW(A2:A28)),IF((A1:A27<0)*(A2:A28>= 0),ROW(A1:A27))))+1,0). for a bunch of data in column A. With limited knowledge of excel I'm not exactly sure how this formula works, so I'm not sure how to tell it to ignore all blank cells. I attached an example that shows how it fails with the blank cells

View 2 Replies
View Related
Jan 27, 2010

I want to count the number of consecutive text entries in the one column. Skip a certain entry eg: NA, then count the next string of the same entry. eg:

Apples

Apples

Apples

Apples

NA

NA

NA

Apples

Apples

Apples

NA

So the output that I am after will be counting the number of Apples in a row 4 & 3 but skipping NA.

View 9 Replies
View Related
Jul 1, 2014

I have 2 Columns. One column represents calendar dates and the other column represents numbers between 0 and 7.

Therea re 10000 rows in this table.

I would like to count how many consecutive days I observe certain numbers numbers ( i.e 3+, 4+, 5+, etc)

View 3 Replies
View Related
Jan 8, 2010

I'm trying to do is to get Excel to look at the dates and, if they are consecutive (Fri - Mon inclusive is also counted as consecutive) I need Excel to tell me that this is one occasion. It then needs to count each seperate occasion.

I've attached an example with each different occasion colour coded. In the example there a five occasions. Thus the Bradford Factor would be 187.5 (5*5*7.5).

View 2 Replies
View Related
Oct 2, 2013

I'm trying to find a way to take a data set and write an excel equation/s to find out how many times in that column of data a certain result (number or letter) occurs consecutively for more than 5 (hoping that this is also customizable) times. For example....

DATE

USER A

1/1/2013

NO

[Code]....

Above are two columns, one with the date and another with the data I'd like to search through. So I'm hoping that I can write an equation/s that tells me how many times a certain value, in this case I'm looking for "No" occurs more than 5 times consecutively in the line of data. For example, for this particular data set, the final answer would be 2. There are only two instances where 5 or more cells with a "No" value follow each other.

View 3 Replies
View Related
Jan 13, 2014

Need a formula for counting how many consecutive cells where the value is gretaer than or equal to +1

For example 1,

J18 = 7

K18 = 9

L18 = 3

M18 = 2

N18 = 8

O18 = 1

P18 = 8

Q18 = -17

In the above example the result would be 7

Example 2:

J12 = -22

K12 = 9

L12 = 4

In the above example the result would be 0 since the 1st value was -22

View 3 Replies
View Related
Feb 7, 2012

Say the Range is (D3:M3) there is data in D3,F3,J3,K3,L3, and M3. I want to count consecutive cells with data from M3 backwards to D3. In this scenario the sum would be 4.

View 7 Replies
View Related
Mar 27, 2014

I wanted to count the number of instances that Matt's been late. If there are consecutive timestamps (ex. 600-620, 620-640) that he's been late, I wanted Excel to display 1 and then I'll just sum it up. Or if Excel can do this directly, add all the instances because what I actually need is the total per person.

In this example, I would need a result of 4 instances.

tell if the blank cells will affect the formula or if I still need to do something about them.

View 3 Replies
View Related
Mar 25, 2012

Is there a formula that can count the number of words in a cell?

View 3 Replies
View Related
Jan 19, 2014

1. I use this formula to count a number of items in a column per month: =SUMPRODUCT(--(MONTH(B6:B500)=8))

I also have another column C where I put some descriptors against the values in column B. I need a formula to calculate a number of items in Column B, except for those items, which are equal to XYZ in column C.

2. I also calculate a number of items, which were completed in a given month. Completion is marked with "Y" in a separate column (D). I use this formula: =SUMPRODUCT((YEAR(B6:B999)=2013)*(MONTH(B6:B999)=12)*(K6:K999<>"Y")). I can't make it count a number of completed items in January. C

View 8 Replies
View Related
Jan 14, 2014

I'm looking for the easiest way to count the number of occurrences within a cell range.

The formula that I'm currently using is:

=COUNTIF(D$5:D$8,"a*")

This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;

When I want to find multiple values in the range and count them all, I use this formula:

=COUNTIF(D$5:D$8,OR("a*","b*","c*"))

View 9 Replies
View Related
Oct 25, 2007

Basically I want to calculate each team's home winning/drawing/losing streak. Here is a sample of the table (I've simplified it a bit and added fictional results):

(See Attached)

So, I want a formula that can tell me a given team's (e.g. Arsenal's) latest result, and how many previous consecutive results of that kind there have been. For example, for Arsenal, the result would be something like "W2" (i.e. Won the last two games). At the moment, the best I can do is to get the TOTAL NUMBER of a given team's latest result using this formula:

=LOOKUP("ARS",B:B,C:C)&SUMPRODUCT(--(B1:B13="ARS")*(C1:C13=LOOKUP("ARS",B:B,C:C)))

(Which also returns "W2").

View 4 Replies
View Related
Nov 18, 2008

I have an excel spreadsheet to record employee holiday and sickness figures.

It is set on as a grid e.g. column A stores all the dates and then employee names are used as column header.

One of the triggers I need to use is where, an employee has been absent 28 consecutive days. When an employee is absent I simply enter 'ABSENT' against there name.

Countif will count the number of time absent appears in the column however I need it to only recognise it if it is only 28 days in a row.

View 8 Replies
View Related
Jan 4, 2014

I have a spread sheet of hourly temperatures which is broken down into year, month, day, time, and temperatures. I would like to find the top ten longest stretches of sub-zero temperatures in number of hours and the time and date in which they started and ended. I have attached a spreadsheet for your perusal..

View 3 Replies
View Related
May 5, 2009

E20 with the series of numbers in tenth, and with five numbers on one cell. Between 01-37. How do I write a formula on column R to identify any repeated numbers from E20 with E21? In the chart it shows number 10 is repeated from E21, so I place a check mark on the same row as E20 on column R.

On column S how do I write a formula to identify numbers within E20 to see if there are any consecutive numbers? On E20 there is none, on E21 there is one 10-11, and column S will let me know if there is a consecutive by placing a check mark.

View 9 Replies
View Related
Nov 24, 2009

In my worksheet, I have a column of ascending dates from BD7 to BD22, with BD22 being the current date. In column BN, I track whether a process is "Over" or "Under" a limit.

The values in column BN are text with either of those names.

I need to return two values in cells BO7 and BO8 respectively:

1) The number of consecutive days back from the most current day that have been "Over" the limit

2) The date going from the most current day that the first "Over" occurred

View 9 Replies
View Related
Jan 13, 2007

to create with the default excel functions the following calculator. I need to calculate the maximum number of positive numbers which happen in a row and the maximum number of consecutive negative numbers. For example in the following list of numbers there are a maximum of 7 consecutive positive and a maximum of 6 consecutive negative numbers:

6 000

6 360

6 742

7 146

7 196

7 628

8 086

-4 071

7 898

-4 186

8 121

8 608

-4 562

to make a formula which will calculate the maximum length of positive and negative numbers in a row? I attached this table to the post.

View 9 Replies
View Related
Dec 27, 2013

I'm looking at test results and would like to know how many times in a row correct answers were given before getting one incorrect. The way I have this currently set up, is if a student has the answer correct it returns a "TRUE" value, and a "FALSE" value for an incorrect answer. A typical test may have 50 questions, so I would have either "TRUE" or "FALSE" values in rows A1 through A50.

So I've been trying to figure out a formula that counts the number of times "TRUE" rows repeat 'consecutively' in a given column, in this scenario column A. The formula would need to check the current row and if "TRUE" check the row immediately after, if that row is also "TRUE" then check the row immediately after that, until it runs into a "FALSE" string, then display that total count of consecutive "TRUES" in another cell. I understand there may be duplicate results but I'm only really interested in the longest value, so duplicates can be ignored.

If it makes the formula easier, each possible result can be assigned to it's own cell. Let's assume the test has 50 questions. So if a student got all 50 questions correct the result count would be 50 in cell B50, or if they got 25 correct before the first incorrect answer, cell B25 would say 25, etc. I'm guessing I could then easily query this column for the largest value to come up with what I need at a glance.

View 5 Replies
View Related
Apr 10, 2008

i've got a sample database (attached) with a userform for inputting of data. What i want it to do is automatically generate the next number and add it to textbox "our ref" on the userform. the number is in Col C. This is the code i've tried using the code highlighted in red which doesnt work.

View 12 Replies
View Related
Oct 11, 2012

What I am trying to is to count the number of times a certain number or character appears (either on its own or in a batch of consecutive cells containing that number/character) in a column.An example might clarify things (for reasons of brevity I will write the columns in rows):

If a column looks like (each 1-digit numbers / characters being a consecutive cell) 0 0 X X 0 0 X and I am counting for X, then I should get 2. If my column is X X 0 X X 0 X 0 0, then I should get 3. If my column is 0 X X 0 0 X 0 then I should get 2. If my column is X X 0 X X 0 X then I should get 3. Is there a formula to perform that calculation?

View 1 Replies
View Related
Aug 13, 2014

I have a range of say B1:Z2. In row B1:Z1 I have dates in the format dd-mmm-yy. In row B2:Z2 I indicate presence of a person by "P". This is at infrequent appearance i.e. p,p,p,blank,blank,blank,p,p,p,p,p,blank,blank,p,p,blank,p,p,p, etc. I am trying to find or workout a formula that would find the most repetitive Ps in row B2:Z2 and give me the sum thereof. I.e, from the above example it should be 5. It does not have to count specifically P. Something that counts the most non-blank sequence of cells in the range should also do.

View 8 Replies
View Related
Jul 5, 2006

Using a Dynamic Named Range "Data", I would like a Formula to return the Row

Number of the "LAST" instance of a duplicate numeric value - repeating twice

consecutively in the same column.

View 10 Replies
View Related
May 2, 2007

Let's say I have a column A with the following values.

30

40

30

60

-20

-10

-50

-60

-70

120

320

20

-40

-30

40

How can I have 2 cells display:-

i)highest streak of positive numbers = 4

ii)highest streak of negative numbers = 5?

Also, how can I have another 2 cells display:-

iii)the sum of the highest streak of positive numbers = 160

iv)the sum of the highest streak of negative numbers = -210

View 6 Replies
View Related
Jan 16, 2010

I'm trying to create a formula (in Sheet 1) that will return the most recent number of cells containing a value (i.e. not empty) in Sheet 2 Range AD2:A367 greater than zero.

I've tried using COUNTIF but that only adds the individual values, it doesn't 'start from zero again' when a zero value appears.

View 9 Replies
View Related
Jan 12, 2007

how to built a formula or a VBA code which would tell me how many times a range of a certain amount of consecutively stnading cells in a given row there is present, where each of the cells belonging to these five cells fulfills a certain criterion.

Example:

How many times does the range of 5 consecutive cells appear in range A1 to P2, where every cells belongng to these 5 consecutive cells would have a value graeater than 10.

View 8 Replies
View Related
Mar 3, 2007

I work on some High-throughput Projects analyzing biological samples. The software we use for analysis spits out the data into excel. This is all well and good, being mostly automated. However, my issue arises when I get the output data. It's simply one long list of values in a single column. This could potentially be 384 entries. Depending on the parameters of our experiment, I'd like to be able to take a set number of consecutive entries and transpose them to another worksheet. However I don't know how to loop the function to get the specific entries transposed to their specific worksheet. I'm pasting a recorded macro I made to illustrate my point.

Sub HTSoutput1()

Range("B2:B9").Select

Selection.Copy

Sheets("K562").Select

Range("B2").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=True

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "2330"

Sheets("Nuclei").Select

Range("B10:B17").Select.............

View 9 Replies
View Related
Nov 22, 2007

I've tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it - I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....

View 9 Replies
View Related
Feb 3, 2014

I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls

View 1 Replies
View Related
Jul 10, 2014

I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.

I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.

------ UPDATE

In response to using CountIFS I have also tried-

=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.

View 10 Replies
View Related
Oct 13, 2011

I'd like to take data in the range from B2:B500 and in C2 sum from B2:B9 and then in C3 sum from B10:B17 and in C4 sum from C18:C25 and so on.

View 2 Replies
View Related
Apr 12, 2013

I have a user form with 125 checkboxes and if checked would like them to display one message box containing a specific message for that the checkbox slection, but all messages in one message box.

I have one command button that will list each message box individually, using code like this -

If CheckBox12 = True And CheckBox5 = True Then MsgBox "You would need to see your manager"

Each message box individually is time consuming and it would be better if they all displayed in one box. Of the 125 check box options, 7 or 8 will only be selected at any given time.

View 4 Replies
View Related
Dec 17, 2013

The formula in column B counts the number of values that are

View 9 Replies
View Related
Nov 18, 2009

create a formula that counts consecutive rows that have wins and losses.

A B

1 Game 1 Win

2 Game 2 Win

3 Game 3 Win

4 Game 4 Lose

5 Game 5 Lose

6 Game 6 Win

7 Game 7 Lose

So Consecutive wins = 3

Consecutive losses = 2

View 9 Replies
View Related
Mar 30, 2014

I would like a formula to track consecutive odd/even digits.

Odd= 1,3,5,7,9 consecutive 135,357,579,791 any order of those values.

example 135,153,315,351,513,531

Even= 0,2,4,6,8 consecutive 024,246,468,680 any order of those values.

example 024,042,204,240,420,402

If no CE or CO, return N

Sheet1BCDEF1103/29/14208CE1203/29/14913CO1303/28/14462CE1403/28/14795COExcel 2007

View 9 Replies
View Related