# how to count cells with one number between two different numbers

Copper Contributor

# 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

15 Replies

# Re: how to count cells with one number between two different numbers

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!

This will help all forum participants.

# Re: how to count cells with one number between two different numbers

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

# Re: how to count cells with one number between two different numbers

Using any Excel

``=SUMPRODUCT( --(INDEX(A:A, MATCH(18,A:A,0)):INDEX(A:A, MATCH(19,A:A,0))=5) )``

# Re: how to count cells with one number between two different numbers

And is it possible without Excel365?

# Re: how to count cells with one number between two different numbers

What are these "--" and ":" mean, please?

# Re: how to count cells with one number between two different numbers

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.

# Re: how to count cells with one number between two different numbers

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.

# Re: how to count cells with one number between two different numbers

How about the attached version? It uses a helper column.

# Re: how to count cells with one number between two different numbers

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

# Re: how to count cells with one number between two different numbers

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

# Re: how to count cells with one number between two different numbers

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.

# Re: how to count cells with one number between two different numbers

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!

# Re: how to count cells with one number between two different numbers

The OP can decide which version they like best.

# Re: how to count cells with one number between two different numbers

I don't think I am in contention.

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

eusprig-2004-after-dinner-speech.pdf

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!