Excel: count the number of cells in a column until their sum is greater than a set value
Adam Reynolds
I have a list of consecutive events in one column and their durations in the adjacent column. I want to count the number of times a particular event occurred in a certain length of time.
For example:
Col Event Duration
1-- Contr 8
2-- Relax 5
3-- Contr 12
4-- Relax 6
5-- Contr 10
6-- Relax 5
In this example I want excel to start from row 6 and go backwards, summing durations until the value is over 30 and return the number of times Contr occured over that time period. Here the answer is 2.
Here's a link to an example spreadsheet with more realistic values: https://1drv.ms/x/s!AiOl_zwCwrAmgcgQqCPaOY5WzMZTGQ
(The threshold value would be 900 instead of 30.)
Thanks. Hope I asked the question right.
Tom Sharpe
I would have to use a helper column to work out the totals under the current row. I don't mind if the sum including the current row is 30 or more, as long as the sum under it is less than 30.
So in D2:-
=SUM(C3:C$100)
Then it's just a countifs:-
=COUNTIFS(B2:B100,"Contr",D2:D100,"<"&30)
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Not able to parse Map with Enum to Json in Play Scala
- Next: Specify worksheet in XSLT export filter for OpenOffice Calc
Related
Related Related
- 1
Count cells in a column between values until specific value reached in Excel
- 2
count the number of values greater than each value in a column of an array in r
- 3
How to count number of cells greater or less than 0 (zero) between two dates in Excel?
- 4
Excel: count numbers greater than 3 in strings in range of cells
- 5
Excel Count number of times in range that cells in column A are less than corresponding cell in column B
- 6
Find the nearest number greater than a specific value among cells
- 7
Excel Highlight Cells Greater than
- 8
Number of Subarray whose sum greater than given value
- 9
How to count cells in a range with a value less than another cell in excel?
- 10
Custom Function to Count Number of Used Cells in a Column in Excel 2010
- 11
how to count of the number of filled cells in a column in an excel sheet
- 12
how to count of the number of filled cells in a column in an excel sheet
- 13
How to create a cumulative sum column in python if column value is greater than other value
- 14
the sum of cells that meet a greater than and less than criteria
- 15
Elegant way to count number of elements in each column of a matrix that are greater than those in every other column?
- 16
Microsoft Excel how to select all cells that meet certain condition (i.e greater than a value)
- 17
MySQL count rows where column value is same and select them where count is greater than 2
- 18
Excel, if column is greater than zero and another column contains "Competitor", subtract the value of another column by one
- 19
UPDATE column if column is greater than a value
- 20
InfiniteScroll + isotope : loadNextPage until item count greater than 3
- 21
SUMIF Value is Greater Than Value in Previous Column
- 22
Count observations greater than a particular value
- 23
Linq: Where count greater than value
- 24
Count items greater than a value in pandas groupby
- 25
Finding number of cells in a Column in Excel
- 26
mysql value greater than percent of number of values
- 27
Pandas change column value if greater than len
- 28
excel 2013 pivot table count sums greater than and less than
- 29
How to scan a pandas dataframe for all values greater than something and returns row and column number corresponding to that value?
Comments