Count the most recent x number of dates that meet certain criteria

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
915
Office Version
  1. 365
Platform
  1. Windows
I need help with a formula that counts the most recent 25 items closed.
The result I am looking for is a TRUE/FALSE in column F (Last 25 Closed)
The criteria is based on todays date (H1), Date Closed (Column B), and whether there is a "Yes" in Member of Group (Column G)

Thank you to anyone who can help with this!

Book1
ABCDEFGH
1Date OpenedDate ClosedOn TimeWeek Number ClosedDays to CompletionLast 25 ClosedMember of Group2023-02-27
225-Aug-211-Feb-23Y5376TRUEYes
31-Jun-22NFALSENo
46-Dec-2131-Jan-23N5302FALSENo
512-Jul-22NFALSEYes
628-Mar-224-Apr-22N146FALSEYes
71-Apr-2224-Aug-22N34104FALSEYes
818-Apr-2227-Jun-22N2651FALSEYes
94-Apr-2224-Nov-22N47169FALSEYes
1021-Apr-2224-Nov-22N47156FALSEYes
113-May-2223-Nov-22N47147FALSEYes
129-May-2224-Nov-22N47144FALSEYes
1311-May-2223-Nov-22N47141FALSEYes
1417-May-2216-Nov-22N46132FALSEYes
1517-May-2224-Aug-22N3472FALSEYes
1617-May-2224-Aug-22N3472FALSEYes
1728-Jul-2224-Aug-22N3420FALSEYes
1810-Aug-2224-Aug-22N3411FALSEYes
1926-Aug-2229-Aug-22N352FALSEYes
206-Sep-2216-Jan-23N395FALSEYes
2126-Sep-2212-Oct-22N4113FALSEYes
2227-Sep-222-Dec-22N4849FALSEYes
2311-Oct-2217-Oct-22N425FALSEYes
2424-Oct-2231-Oct-22N446FALSENo
2525-Oct-2223-Nov-22N4722FALSENo
2628-Oct-2220-Jan-23N361TRUEYes
278-Nov-222-Dec-22N4819FALSENo
2810-Nov-222-Dec-22N4817FALSEYes
2915-Nov-222-Dec-22N4814FALSEYes
3021-Nov-2229-Nov-22N487FALSEYes
3121-Nov-2229-Nov-22N487FALSEYes
322-Dec-2229-Dec-22N5220FALSEYes
336-Dec-2216-Jan-23N330FALSENo
348-Dec-2216-Jan-23N328FALSEYes
3513-Dec-2216-Jan-23N325FALSEYes
3616-Dec-2216-Jan-23N322FALSENo
3720-Dec-2220-Jan-23N324TRUEYes
389-Jan-239-Feb-23N624TRUEYes
399-Jan-23NFALSENo
409-Jan-2312-Jan-23N24FALSENo
4113-Jan-2316-Jan-23N32FALSEYes
4211-Jan-2316-Jan-23N34FALSEYes
4311-Jan-2312-Jan-23N22FALSENo
4412-Jan-2323-Feb-23N831TRUEYes
4512-Jan-2316-Jan-23N33FALSENo
4613-Jan-2319-Jan-23N35TRUEYes
4723-Jan-2327-Jan-23N45TRUEYes
4823-Jan-23NFALSENo
4923-Jan-2327-Jan-23N45TRUEYes
5023-Jan-2330-Jan-23N56TRUEYes
5123-Jan-2330-Jan-23N56TRUEYes
5223-Jan-238-Feb-23N613TRUEYes
5325-Jan-238-Feb-23N611TRUEYes
5427-Jan-232-Feb-23N55TRUEYes
5527-Jan-2315-Feb-23N714TRUEYes
5627-Jan-2327-Jan-23N41TRUEYes
5727-Jan-2327-Jan-23N41TRUEYes
5827-Jan-233-Feb-23N56TRUEYes
5931-Jan-2321-Feb-23Y816TRUEYes
603-Feb-237-Feb-23Y63TRUEYes
619-Feb-23FALSEYes
6210-Feb-2314-Feb-23Y73TRUEYes
6310-Feb-2321-Feb-23Y88TRUEYes
6410-Feb-23FALSENo
6510-Feb-2313-Feb-23Y72TRUEYes
6614-Feb-2315-Feb-23Y72TRUEYes
6723-Feb-2324-Feb-23Y82TRUEYes
6823-Feb-23FALSEYes
6924-Feb-23FALSEYes
7024-Feb-2327-Feb-23Y92TRUEYes
Sheet1
Cell Formulas
RangeFormula
H1H1=TODAY()
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
How about
Excel Formula:
=AND(G2="Yes",B2>=AGGREGATE(14,6,$B$2:$B$70/($G$2:$G$70="yes"),25))
 
Upvote 0
Solution

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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