how to count cells with one number between two different numbers

Copper Contributor


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

15 Replies


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:



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.


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


Using any Excel

=SUMPRODUCT( --(INDEX(A:A, MATCH(18,A:A,0)):INDEX(A:A, MATCH(19,A:A,0))=5) )
And is it possible without Excel365?
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.


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

@Peter Bartholomew 

But what if the column ends with an 18 and then a mixture of 5's and other numbers, but no 19?


@Peter Bartholomew 

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.

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!

@Peter Bartholomew 

The OP can decide which version they like best.


I don't think I am in contention.

I found a reference to an after dinner speech on spreadsheets from 20 years ago.


It lays out very clearly why nothing I write could appeal to more than 10% of forum members.  There are so many things about spreadsheets that, as an abstract thinker, I find detestable.  They are precisely the reason that the spreadsheet, Excel in particular, is so successful!