Count Consecutive Values By Condition
Jan 26, 2010
I have a spreadsheet with employees performance results for the last 12 months. Column A contains name, columns B to M contain text "Gold", "silver" or "bronze". I need to count how many consecutive months thay have achieved "Gold" status BUT must include latest month.
So, if column M contains "silver" or "bronze", the consecutive answer (column N) would be 0. If col M = "Gold" but L doesn't then N = 1. If M and L = "Gold" then N = 2 etc etc.
Is there a formula I can use in column N for this without doing 12 "if" functions?
I think this needs to be an array formula but the ones I've tried I can't get to work.
View 6 Replies
ADVERTISEMENT
Dec 28, 2009
I'm looking for a VBA code or formula that will count consecutive values in a column.
Please look at my example sheet.
View 8 Replies
View Related
Feb 4, 2010
I'm trying to use this formula to count consecutive values but somehow it doesn't work properly.
Does anyone see what's going wrong here or is there a better formula to this?
View 11 Replies
View Related
Sep 22, 2006
I have a range of binary numbers and would like to count the number of times the value of 1 appears BUT there must be at least 4 consecutive 1's.
Here's an example:
1
1
1
1
1
1
0
1
0
1
1
1
The final count should be 6.
View 6 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
Jun 17, 2012
I'm looking to get a formula to counting unique values listed in a column depending on a condition also find the attached file for more details
[URL]
View 9 Replies
View Related
Aug 13, 2012
I need to count the amount of unique values in a range, but only count unique values that also have a seperate condition. To illustrate with the 2 column dataset below....
Colour Letter
Yellow A
Yellow A
Green B
Yellow B
Blue A
Blue A
Yellow C
For this set of data i want 3 seperate cells for each 'Letter' telling me that, for 'A', the answer is 2 unique values (i.e. Yellow and Blue). For 'B' there is also 2 unique values (i.e. Green and Yellow) and finally for 'C' there is just 1 unique value (i.e. yellow).
View 2 Replies
View Related
Oct 24, 2007
=SUBTOTAL(2,A2:A100) can be used in combination with any other formula so that when the data is filtered using autofilter it will only count values greater than zero.
I have attached a worksheet with the example.
In the attached workbook subtotal formulas are used in Row1 for sum and row 2 for count.
When the data is filtered using code "DUP" in column D, the subtotal in cell C2 is "4", whereas in the specified range there is a positive value in only 1 cell that is $1050 in cell C99.
Is there some combination that can be used with SUBTOTAL formula so that it will only count values greater than zero when data is filtered.
View 2 Replies
View Related
Jan 15, 2014
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
View 7 Replies
View Related
Mar 18, 2008
I need to count the maximum amount of consecutive crosses that appear in a list. For instance:
x
x
x
x
x
x
x
x
x
the result would be 4, because the highest amount of consecutive crosses in this list is 4 times.
Would anyone mind providing me with a formula for this, please? The list is from C5:C52.
View 10 Replies
View Related
Jun 24, 2007
I have a daily column of numbers of approx 600 rows and the number is either a 0 or 1 and the 0 or 1 are in a random order in each row like:
1
1
1
0
1
0
0
0
1
0
0
1
I would like to find the min number of rows with 1, the max number of rows with 1, the totals of consecutive rows with 1 ie 3 consecutive rows of 1 appear 4 times, 4 consecutive rows appear 6 times etc and the average of the consecutive rows with 1.
View 9 Replies
View Related
Apr 2, 2008
I have to calculate bonus payments for people working O/S. I have a 5 year calendar in month blocks (60 months/columns). In each month there is a percentage 'time O/S' figure.
If an employee is O/S for 3 consecutive months they get bonus 'A', 6 consective months bonus 'B', etc. How can I evaluate the 60 columns, returning the number of times 100% occurs in 3 consecutive months/columns. I have found examples of similar solutions but they will return a value of 3 if there are 5 consecutive months of 100%...
ie. 100 100 100 100 100
= 100 100 100 *** ***
& *** 100 100 100 ***
& *** *** 100 100 100
= 3
but I need it to equal 1
View 14 Replies
View Related
Sep 22, 2011
I'm trying to come up with an array formula to count the consecutive declines or increases from the last value in a column, going from bottom to top. The real world application is a list of daily stock prices down a column, and counting from the most recent day, how many consecutive days the price declined or increased.
I got some hints in the following post but can't get it evaluate the resulting logical array in reverse order (which is needed since column addresses are evaluated top to bottom, not bottom to top).
Count consecutive increase in values
Example for consecutive increases leading to last value
49
50
51
52
53
49
55
56
I need a formula to return 2, the number of increases from 49 to 56.
View 5 Replies
View Related
Jul 25, 2009
I am a remodeling contractor trying to monitor trips my employees are making to the lumberyard. I am able to export to excel from our accounting program a column of dates that invoices are made and another column that tells me whether trips were made before 8:30, between 8:30 and 3:30, after 3:30, or whether materials were delivered involving no trip. So I can count "time of day" trips. I also want to know if multiple trips were made in a single day, or if trips were made 2, 3, 4, 5, etc. days in a row.
Column 1 Column 2
Monday, July 6 2009 Before 8:30
Monday, July 6 2009 Between 8:30 and 3:30
Tuesday, July 7 2009 Between 8:30 and 3:30
Wednesday, July 8 2009 Before 8:30
Friday, July 10 2009 After 3:30
Monday, July 13 2009 Before 8:30
Wednesday, July 15, 2009 Before 8:30
In this example, I have 2 trips made on a single day; 1 instance of trips made 3 days in a row; 1 instance of trips made 2 days in a row (Friday, and then again on Monday since we don't work weekends) and 1 instance of 1 trip made on an isolated day. Additionally, we have 4 trips made before 8:30; 2 trips between 8:30 and 3:30 and 1 trip made after 3:30. Columns for a single job could range to 30 or 40 trips or more, and we have 6-12 jobs running at any one time, so I could be looking at data for all of those jobs once a month if I can figure out how to make it easy to do. I have figured out how to count up the time of day trips (but included the example here for a fuller picture of what I'm trying to do) but cannot figure out how to count the number of 2,3,4,5 etc.-day-in-a-row trips that are being made.
View 9 Replies
View Related
Jun 24, 2007
I have a daily column of numbers of approx 600 rows and the number is either a 0 or 1 and the 0 or 1 are in a random order in each row like;
1
1
1
0
1
0
0
0
1
0
0
1
I would like to find the min number of rows with 1, the max number of rows with 1, the totals of consecutive rows with 1 ie 3 consecutive rows of 1 appear 4 times, 4 consecutive rows appear 6 times etc and the average of the consecutive rows with 1.
View 5 Replies
View Related
Aug 13, 2008
I am preparing a attendance sheet. I am using 1 & 0 for present(=1) & absent(=0). I want to find out if a student has been absent for three consecutive days and if there is three consecutive 0 then the formula should return the value 0 ( the student gets 0 if he is absent for 3 consecutive days ) otherwise it should add all the 1s in the row. i.e
1 1 1 0 0 1 1 0 = 5
10 0 0 1 1 0 0 = 0
View 5 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
Dec 15, 2011
I need a formula that will calculate the most recent consecutive years.
The actual data has multiple ID# and the consecutive years need to be matched to the ID#
So far I have =COUNTIFS(ID_NUMBER, A2, EXPIRATION_DATE, ?????)
A friend of mine suggested that I count backwards from the most recent date and stop counting when there is a break of one year. That is where I get stumped. It doesn't need to be a year day and date, I just need to compare the YEAR portion of the date.
The example data is provided below the consecutive year value on the sheet was calculated by hand and is the correct value that needs to be returned.
ID # Transaction Date Transaction Type Registration Package Amount Paid Expiration Date Years Paid Inspected Consecutive Years 57 8/8/1996 NEW SHIRT+DISC $15.00 8/8/1997 8 Y 3 57 9/3/1997 RENEW DISC $15.00 8/8/1998 8 Y 3 57 9/8/1998 RENEW SHIRT $15.00 8/8/1999 8 Y 3 57 6/13/2001 RENEW SHIRT $15.00 6/13/2002 8 Y 3 57 5/6/2005 RENEW DISC $15.00 5/6/2006 8 Y 3 57 5/12/2009 RENEW DISC $15.00 5/12/2010 8 Y 3 57 3/7/2010 RENEW DISC $15.00 5/12/2011 8 Y 3 57 1/4/2011 RENEW SHIRT $20.00 5/12/2012 8 Y 3
View 9 Replies
View Related
Oct 31, 2013
I need an equation that will return a 1 in column F if data is available (cell is populated) in 2013 or last two years (2012 & 2013) or last three years (2011 & 2012 & 2013) or last four years or last five years. A zero counts as data.
Data is set up as:
Years in columns
2009
2010
2011
2012
2013
Count
product 1
34
32
33
31
35
1
[Code] ......
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
Dec 11, 2009
Split from Count Of Groups Where Last Character Is Unique
The A1 B1 C1 were not the data items in the table, but the cell addresses. The new title of the thread does not reflect what I am asking either. The focus of the question is the counting of groups, not counts of cell contents.
View 5 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
Jan 18, 2009
I have a sheet that lists customers in column A and then each customers Distribution & Sales over a period of time. The spreadsheet then flags if the customer has sold out in any month. I now require a count of maximum consecutive sellouts for each customer. For example:
Customer A S/O S/O nil nil S/O
Customer B S/O S/O S/O nil nil
Customer C nil S/O S/O S/O S/O
In the above example Customer A would have 2, B 3 and C 4 sellouts. I'm looking at about 10K customers I require for each product.
View 3 Replies
View Related
Aug 7, 2013
I have a range of data but the important pieces are
Column B - list of employee IDs.
Column C - Date
I have sorted the data by column B with secondary sort for column C oldest to newest date.
I want to know the number of instances where an employee has 3 consecutive days in column C. Only count 3 consecutive. If the consecutive days go over 3 (such as 4 or 5 days) I do no want to count these.
View 8 Replies
View Related
Feb 25, 2007
I have a table of data as below recording daily foreign exchange rates.
Column A = Time and date
Column B = USA
Column C = UK
Column D = Aust.
Column E = Japan
Column F = Euro
Time and date USA UKAust.JapanEuro
04-Jan-20060.68390.39180.924379.470.5693
05-Jan-20060.68660.39050.918779.720.5666
06-Jan-20060.68710.39150.919579.720.5675
Then in column H i have a formula that works out if the rate has exceeded a specified base rate, if it has i display Threshold Exceeded and if it hasn’t i display No Action Required.
OK from here i want to count the number of times in the month the Threshold has been exceeded. I can do this by using the countif formula.
Now this is the section i need help on
For a specified time, say a month, i want to count the maximum number of times the Threshold has been exceeded consecutively.
E.g
Threshold Exceeded
Threshold Exceeded
Threshold Exceeded
Threshold Exceeded
Threshold Exceeded
No Action Required
No Action Required
Threshold Exceeded
Threshold Exceeded
Threshold Exceeded
Result = 5 (max number of consecutive Threshold Exceeded” for a specified time frame)
View 9 Replies
View Related
Apr 12, 2014
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
View 9 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
Jun 2, 2014
I couldn't figure out how to change this function to allow for weekends.
Function to count consecutive dates as one occasion
=--((NETWORKDAYS(SUM(c1)*(B1=B2),c2)-1)>1)
Column A Column B Column C Column D ( I would then use this column in a pivot table to calculate)
pat name pat id date of service # Admissions
[code]....
View 4 Replies
View Related
Mar 13, 2014
Formula to count the number of consecutive zero starting from the last cell with non zero value.
View 14 Replies
View Related
