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 through the formula?
Thank you very much
Using any Excel
=SUMPRODUCT( --(INDEX(A:A, MATCH(18,A:A,0)):INDEX(A:A, MATCH(19,A:A,0))=5) )- What are these "--" and ":" mean, please?
Let's say cell A1 contains 37 but as a text value, not a real number.
-A1 converts that text value to the negative number -37.
--A1 converts that to the positive number 37.
: is simply the separator between the first and last cell in a range, as in A1:B3.
Both INDEX(...) parts return a cell. INDEX(...):INDEX(...) returns a range.
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
To count the occurrence of the value 5 that appears between the values 18 and 19 in column D, you can use the following formula:
=COUNTIFS(D:D,5,D2:D100,">18",D2:D100,"<19")
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful!
This will help all forum participants.
