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

medelste

New Member
Joined
Jan 26, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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
    Screenshot 2021-01-26 143736.png
    22 KB · Views: 13

Some videos you may like

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
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 26, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Brilliant, Stephen & Peter, I wish my brain worked like yours! Be well and stay safe.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top