# Count of last *number* from an array between two values

#### medelste

##### New Member
Hi, first time posting so apologies if I'm not doing this correctly and I can clear up my question in some way. I searched the archives but couldn't find this question. If it already exists, please point me to it.

I'm having trouble creating a formula that finds the number of instances that a value falls between two numbers among the most recent 'n' instances of those values in a list. In the image shown, column B contains the maximum value to be counted for each row, and C2 and D2 contain the number of last instances to be queried. The numbers in the gray background are what I hope a formula can output for each cell.

To give two examples using the image: for cell C3, I'd like to find the number of times the values in the data to the right fall within the 1<=x<=2 range over the last 3 dates that a number appears on that row. The last three values on that row are 4, 2, and 3, so only the 2 falls within 1-2, so C3 should return 1 as an answer. For cell D5, I'd like to find the number of times the values in the data to the right fall within the 1<=x<=3 range over the last 6 dates that a number appears on that row. The last 6 values are 2, 3, 2, 0, 1, 3, so the 2, 3, 2, 1, and 3 fall within the 1-3 range, so D4 should return 5 as an answer.

If it helps allow for a simpler/faster formula, the maximum value to be queried (column B) would be 4.

Thank you for any guidance.

Mike

#### Attachments

• Screenshot 2021-01-26 143736.png
22 KB · Views: 13

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### StephenCrump

##### MrExcel MVP
Welcome to the Forum!

ABCDEFGHIJKLMNOPQRS
1
2MinMax367 Jan 20218 Jan 20219 Jan 202110 Jan 202111 Jan 202112 Jan 202113 Jan 202114 Jan 202115 Jan 202116 Jan 202117 Jan 202118 Jan 202119 Jan 202120 Jan 2021
3A12125423423
4B1112102130
5C132531232013
6D13253212334
7E1223012002201
Sheet2
Cell Formulas
RangeFormula
D3:E7D3=SUM((\$F3:\$S3<>"")*(\$F3:\$S3>=\$B3)*(\$F3:\$S3<=\$C3)*(COLUMN(\$F3:\$S3)>=LARGE(IF(\$F3:\$S3<>"",COLUMN(\$F3:\$S3)),D\$2)))

(You'll need to array-enter this formula)

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Another possibility, given that your dates are ascending.

21 01 27.xlsm
ABCDEFGHIJKLMNOPQR
1
2Max3607-Jan-2108-Jan-2109-Jan-2110-Jan-2111-Jan-2112-Jan-2113-Jan-2114-Jan-2115-Jan-2116-Jan-2117-Jan-2118-Jan-2119-Jan-2120-Jan-21
3A2125423423
4B112102130
5C32531232013
6D3253212334
7E223012002201
Count
Cell Formulas
RangeFormula
C3:D7C3=COUNTIFS(\$E3:\$R3,">=1",\$E3:\$R3,"<="&\$B3,\$E\$2:\$R\$2,">="&LARGE(IF(\$E3:\$R3<>"",\$E\$2:\$R\$2),C\$2))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### medelste

##### New Member
Brilliant, Stephen & Peter, I wish my brain worked like yours! Be well and stay safe.

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Glad we could help. Thanks for the follow-up.

Replies
2
Views
145
Replies
0
Views
58
Replies
5
Views
55
Replies
5
Views
69
Replies
8
Views
241

1,127,802
Messages
5,626,984
Members
416,213
Latest member
neflerine

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back