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 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.
- O got it now. But still have the same problem as before. It only counts the first match, not every match in column. I know that the result must be 28, but the result is just 2.
How about the attached version? It uses a helper column.
