Forum Discussion
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
But what if the column ends with an 18 and then a mixture of 5's and other numbers, but no 19?
- 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!
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.
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!