willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 929
- Office Version
- 365
- Platform
- 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!
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 | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Status | Open IR Letter | Closed IR Letter | Cost of Improvement | Estimated Savings | Audit Month | Date Opened | Date Closed | On Time | Week Number Closed | Days to Completion | Last 25 Closed Items | ||||
2 | Closed | N | FALSE | 25 | ||||||||||||
3 | Closed | N | FALSE | |||||||||||||
4 | Closed | N | FALSE | |||||||||||||
5 | Closed | N | FALSE | |||||||||||||
6 | Closed | N | FALSE | |||||||||||||
7 | Closed | N | FALSE | |||||||||||||
8 | Closed | N | FALSE | |||||||||||||
9 | Closed | N | FALSE | |||||||||||||
10 | Closed | N | FALSE | |||||||||||||
11 | Closed | N | FALSE | |||||||||||||
12 | Closed | N | FALSE | |||||||||||||
13 | Closed | N | FALSE | |||||||||||||
14 | Closed | N | FALSE | |||||||||||||
15 | Closed | N | FALSE | |||||||||||||
16 | Closed | N | FALSE | |||||||||||||
17 | Closed | N | FALSE | |||||||||||||
18 | Closed | N | FALSE | |||||||||||||
19 | Closed | 8-Dec-22 | 16-Jan-23 | N | 3 | 28 | FALSE | |||||||||
20 | Closed | 13-Dec-22 | 16-Jan-23 | N | 3 | 25 | FALSE | |||||||||
21 | Closed | 16-Dec-22 | 16-Jan-23 | N | 3 | 22 | FALSE | |||||||||
22 | Closed | 20-Dec-22 | 20-Jan-23 | N | 3 | 24 | FALSE | |||||||||
23 | Closed | 9-Jan-23 | 9-Feb-23 | N | 6 | 24 | FALSE | |||||||||
24 | Open | 9-Jan-23 | N | |||||||||||||
25 | Closed | 9-Jan-23 | 12-Jan-23 | N | 2 | 4 | FALSE | |||||||||
26 | Closed | 13-Jan-23 | 16-Jan-23 | N | 3 | 2 | TRUE | |||||||||
27 | Closed | 11-Jan-23 | 16-Jan-23 | N | 3 | 4 | TRUE | |||||||||
28 | Closed | 11-Jan-23 | 12-Jan-23 | N | 2 | 2 | TRUE | |||||||||
29 | Open | 12-Jan-23 | N | |||||||||||||
30 | Closed | 12-Jan-23 | 16-Jan-23 | N | 3 | 3 | TRUE | |||||||||
31 | Closed | 13-Jan-23 | 19-Jan-23 | N | 3 | 5 | TRUE | |||||||||
32 | Closed | 23-Jan-23 | 27-Jan-23 | Y | 4 | 5 | TRUE | |||||||||
33 | Parking Lot | 23-Jan-23 | ||||||||||||||
34 | Closed | 23-Jan-23 | 27-Jan-23 | Y | 4 | 5 | TRUE | |||||||||
35 | Closed | 23-Jan-23 | 30-Jan-23 | Y | 5 | 6 | TRUE | |||||||||
36 | Closed | 23-Jan-23 | 30-Jan-23 | Y | 5 | 6 | TRUE | |||||||||
37 | Closed | 23-Jan-23 | 8-Feb-23 | Y | 6 | 13 | TRUE | |||||||||
38 | Closed | 25-Jan-23 | 8-Feb-23 | Y | 6 | 11 | TRUE | |||||||||
39 | Closed | 27-Jan-23 | 2-Feb-23 | Y | 5 | 5 | TRUE | |||||||||
40 | Open | 27-Jan-23 | ||||||||||||||
41 | Closed | 27-Jan-23 | 27-Jan-23 | Y | 4 | 1 | TRUE | |||||||||
42 | Closed | 27-Jan-23 | 27-Jan-23 | Y | 4 | 1 | TRUE | |||||||||
43 | Closed | 27-Jan-23 | 3-Feb-23 | Y | 5 | 6 | TRUE | |||||||||
44 | Open | 31-Jan-23 | ||||||||||||||
45 | Closed | 3-Feb-23 | 7-Feb-23 | 6 | 3 | TRUE | ||||||||||
46 | Open | 9-Feb-23 | ||||||||||||||
47 | Open | 10-Feb-23 | ||||||||||||||
48 | Open | 10-Feb-23 | ||||||||||||||
49 | Open | 10-Feb-23 | ||||||||||||||
50 | Closed | 10-Feb-23 | 13-Feb-23 | 7 | 2 | TRUE | ||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L2:L50 | L2 | =IF([@Status]="Closed",ROW()-ROW(INDEX(IARs,1,1))+1>ROWS(IARs)-$N$2,"") |