Search code examples
excelexcel-formulaexcel-2007

Excel: count the number of cells in a column until their sum is greater than a set value

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.

Solution

  • 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)

    (will post screen shot later)