how to count cells with one number between two different numbers

[MichalK55]

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

[#2]

@MichalK55 

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.

[#3]

@MichalK55 

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

[#4]

@MichalK55 

Using any Excel

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

[#5]

And is it possible without Excel365?

[#6]

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

[#7]

@MichalK55 

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.

[#8]

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.

[#9]

@MichalK55 

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

[#10]

@MichalK55 

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

[#11]

@Peter Bartholomew 

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

[#12]

[#13]

@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.

[#14]

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!

[#15]

@Peter Bartholomew 

The OP can decide which version they like best.

[#16]

@Hans Vogelaar 

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!