Formula for Last X number of rows if status "closed"

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
929
Office Version
  1. 365
Platform
  1. Windows
I am not sure what I am doing wrong with this formula. I am only getting a count of 17 rows as True instead of the 25 parameter in cell N2.
I want a count of the last 25 rows with a "Closed" Status

Formula: =IF([@Status]="Closed",ROW()-ROW(INDEX(IARs,1,1))+1>ROWS(IARs)-$N$2,"")

Any help would be greatly appreciated!

Book1
ABCDEFGHIJKLMN
1StatusOpen IR LetterClosed IR LetterCost of ImprovementEstimated SavingsAudit MonthDate OpenedDate ClosedOn TimeWeek Number ClosedDays to CompletionLast 25 Closed Items
2ClosedNFALSE25
3ClosedNFALSE
4ClosedNFALSE
5ClosedNFALSE
6ClosedNFALSE
7ClosedNFALSE
8ClosedNFALSE
9ClosedNFALSE
10ClosedNFALSE
11ClosedNFALSE
12ClosedNFALSE
13ClosedNFALSE
14ClosedNFALSE
15ClosedNFALSE
16ClosedNFALSE
17ClosedNFALSE
18ClosedNFALSE
19Closed8-Dec-2216-Jan-23N328FALSE
20Closed13-Dec-2216-Jan-23N325FALSE
21Closed16-Dec-2216-Jan-23N322FALSE
22Closed20-Dec-2220-Jan-23N324FALSE
23Closed9-Jan-239-Feb-23N624FALSE
24Open9-Jan-23N 
25Closed9-Jan-2312-Jan-23N24FALSE
26Closed13-Jan-2316-Jan-23N32TRUE
27Closed11-Jan-2316-Jan-23N34TRUE
28Closed11-Jan-2312-Jan-23N22TRUE
29Open12-Jan-23N 
30Closed12-Jan-2316-Jan-23N33TRUE
31Closed13-Jan-2319-Jan-23N35TRUE
32Closed23-Jan-2327-Jan-23Y45TRUE
33Parking Lot23-Jan-23 
34Closed23-Jan-2327-Jan-23Y45TRUE
35Closed23-Jan-2330-Jan-23Y56TRUE
36Closed23-Jan-2330-Jan-23Y56TRUE
37Closed23-Jan-238-Feb-23Y613TRUE
38Closed25-Jan-238-Feb-23Y611TRUE
39Closed27-Jan-232-Feb-23Y55TRUE
40Open27-Jan-23 
41Closed27-Jan-2327-Jan-23Y41TRUE
42Closed27-Jan-2327-Jan-23Y41TRUE
43Closed27-Jan-233-Feb-23Y56TRUE
44Open31-Jan-23 
45Closed3-Feb-237-Feb-2363TRUE
46Open9-Feb-23 
47Open10-Feb-23 
48Open10-Feb-23 
49Open10-Feb-23 
50Closed10-Feb-2313-Feb-2372TRUE
Sheet1
Cell Formulas
RangeFormula
L2:L50L2=IF([@Status]="Closed",ROW()-ROW(INDEX(IARs,1,1))+1>ROWS(IARs)-$N$2,"")
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
How about
Excel Formula:
=IF([@Status]="Closed",COUNTIFS(IARs[[#Headers],[Status]]:[@Status],"Closed")-COUNTIFS([Status],"Closed")+N$2>=1,"")
 
Upvote 0
Solution
How about
Excel Formula:
=IF([@Status]="Closed",COUNTIFS(IARs[[#Headers],[Status]]:[@Status],"Closed")-COUNTIFS([Status],"Closed")+N$2>=1,"")

Works perfect! Thank you very much! :giggle:
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,957
Messages
6,175,623
Members
452,661
Latest member
Nonhle

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
Back
Top