Forum Discussion
how to count cells with one number between two different numbers
Hello, In column D, I need to count the occurrence of the value 5 that appears between the values 18 and 19. I don't want to count any other value 5 in this column. Can anyone advise how to do this ...
Using excel 365, it is possible to identify the cells of the 'value' column lying between 18 and 19 with the SCAN function
= LET(
active, SCAN(0, values, LAMBDA(u,v, SWITCH(v, 18, 1, 19, 0, u))),
SUM(active * (values=5))
)
And is it possible without Excel365?
Something similar in terms of the formula can be made to work in older versions of Excel; its just that, for me, such solutions have gone the same way as one's DVD collection!
To convert, the LET variable 'active' becomes a helper range containing
= SWITCH(v, 18, 1, 19, 0, u))where 'u' and 'v' are relative references to the cell above the formula and the data cell to the left respectively. Once the array 'active' becomes a range reference, one can use
= COUNTIFS(values, 5, active, 1)to return the required count of '5's
I'm sorry, I must be dense. I thought the OP wanted to count 5's in between 18 and 19. 17 doesn't come into it as far as I can tell.
But what if the column ends with an 18 and then a mixture of 5's and other numbers, but no 19?
- Seems I did 17-18 as the range and not 18-19 as specified! I took the end of the range as equivalent to a termination. To do otherwise seemed to be more trouble than it was worth. Your point is correct though, I would need to search for a final occurrence of 19 and disable everything from there on!
