I apologize that I cannot articulate this process, but I guess that's what brought me to this forum:
My worksheet is divided into columns as follows:
- A: train number (1072 entries, with some values appearing 4 times, or some 23)
- B: minutes delayed, most values are 0, but some are 3, 4, 9 etc....
- C & D: both irrelevant
- E: train numbers uniquely listed using the following formula
=INDEX( $A$2:$A$1072, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$1072), 0))
- F: the number of occurrences the train number in adjacent E cel
l occurs in column A using following formula
=COUNTIF($A$2:$A$1072, E2)
What I am struggling to do is find the best means of counting the number of cells in column B which contain a value greater than "0" based on a range specified by each individual train number in column A. These numbers have been filtered, and while this could be done in a few hours time tediously going through a number of filters and counting, I think there must be a better system.
To summarize what I am trying to do:
when the value: "1801" appears 4 times in column A, and the adjacent 4 cells have "0" as their value in column B, I would like the return to be "0".
when the number "803" which appears 23 times in column A, and there are 19 cells with a value of "0", and 4 other cells with digits greater than "0" in the adjacent cells in column B, I would like the return to be "4".
The formulas that I currently have in columns D and E have made it so that I don't have to specify any value, which has been useful because most of these threads involve searching for specifics such as "apples" or "John". Is there anyway to find the returns I want without ever having to manually sort by the train number in column A.
Thanks so much for your help
Bookmarks