I am trying to pull all records that have been resolved in the last 7 days. I want to count these records by area.

Here is my Excel workbook.

Sheet 1
Row 1 for Titles:      **Area (column A) | State (Column B) | State Changed Date (column C)**
Row 2:                 Development       | In Progress      | 7/8/2024 7:00 AM
Row 3:                 Testing           | Ready            | 7/9/2024 9:16 AM
Row 4:                 Business Analysts | Closed           | 6/30/2024 9:16 AM
Row 5:                 Testing           | Closed           | 7/9/2024 9:16 AM
Row 6:                 Development       | Closed           | 7/8/2024 7:00 AM

Sheet 2
Row 1 for Titles:        **Area (column A) | Total Closed in Last 7 Days (Column B)**
Row 2:                   Development       | 1
Row 3:                   Testing           | 1
Row 4:                   Business Analysts | 0

I want to perform a countifs that does the following:

• check sheet 1 column a (Area) for all rows that match Sheet 2 column a (Area).
• Once a match is found, count the row only if the date (located in the State Changed Date column of each record) is within the last 7 days

I tried placing this formula in Sheet 2 Column B row 2:

COUNTIFS('Sheet1'!C2:C6,">="&(TODAY()-7),'Sheet1'!A2:A6,A2)

I get #VALUE! in the cell.

I have tried checking the date last but received the same thing.
I can do a COUNTIF(‘Sheet1’!C2:C6,”>=”&(TODAY()-7) and I get a correct total for all rows in the last 7 days. I can do a Countif(‘Sheet1’!A2:A6,A2) and find all rows in each Area. I am struggling putting the two together.