Alright, let's build the exact formula you need to populate your year-by-month summary table using data from the D worksheet. Here's how to adapt your original SUMIFS framework to fit your setup:

Final Formula (Ready to Customize) #

Assuming your summary table has:

• Years in column B (e.g., B3=2017, B4=2018)
• Months (1-12) in row 2 (e.g., C2=1, D2=2, ..., M2=12)
• The target cell for 2017/January is C3

Use this formula in C3, then drag it across and down to fill the entire table:

=SUMIFS(D!$A:$A, D!$<DATE_COLUMN>, ">="&DATE($B3, C$2, 1), D!$<DATE_COLUMN>, "<="&EOMONTH(DATE($B3, C$2, 1), 0), D!$<STATUS_COLUMN>, "Green")

Step-by-Step Explanation #

Let's break down each part so you understand what's happening:

1. Replace Placeholders: Swap <DATE_COLUMN> and <STATUS_COLUMN> with the actual column letters from the D sheet (e.g., if dates are in column B, use D!$B:$B; if status is in column C, use D!$C:$C).
2. Date Range Lock:
   • DATE($B3, C$2, 1) creates the first day of the target month/year (e.g., 1/1/2017 for $B3=2017 and C$2=1).
   • EOMONTH(..., 0) calculates the last day of that same month, ensuring we capture all dates in the full month (no partial days missed).
3. Reference Locking:
   • $B3 locks the column for the year, so when you drag across months, it stays tied to the year in column B.
   • C$2 locks the row for the month, so when you drag down years, it stays tied to the month in row 2.
   • The D!$A:$A, D!$<DATE_COLUMN>, and D!$<STATUS_COLUMN> use absolute references ($) so they don't shift as you copy the formula around.

Alternative (Using YEAR/MONTH Functions) #

If you prefer to explicitly check the year and month from the date column instead of using a date range, you can use this version (though the date range method is more efficient for large datasets):

=SUMIFS(D!$A:$A, D!$<STATUS_COLUMN>, "Green", D!$<DATE_COLUMN>, ">="&DATE($B3,1,1), D!$<DATE_COLUMN>, "<="&DATE($B3,12,31), D!$<DATE_COLUMN>, ">="&DATE(1900,C$2,1), D!$<DATE_COLUMN>, "<="&DATE(1900,C$2,31))

Note: This works by filtering for the target year first, then the target month (using a dummy year 1900 to isolate the month value).

Critical Checks #

• Make sure the date column in the D sheet is formatted as actual dates (not plain text). SUMIFS won't correctly evaluate text-based dates.
• Double-check that your status column uses exactly the text "Green" (case-insensitive in Excel, but typos will break the formula).

内容的提问来源于stack exchange，提问作者theteague