如何用单个引用单元格批量修改多公式的日期年份及设置J4单元格引用

Got it, let's fix those static SUMIFS formulas so you never have to manually update dates again. Here's a straightforward, scalable solution tailored to your needs:

Step 1: Replace Hardcoded Years with a Cell Reference #

Instead of typing 2018 directly in each formula, we'll pull the year from cell J4 (per your request). This lets you update the year once, and all formulas will reflect the change automatically.

Step 2: Use Dynamic Date Functions for Month Start/End #

Ditch the fixed end dates (like 2018-1-31 or 2018-2-28) and use Excel's DATE and EOMONTH functions to generate accurate start and end dates for any month/year.

Modified Formula for E8 (January) #

=SUMIFS(D$44:$D$84,$B$44:$B$84,">="&DATE(YEAR(J4),1,1),$B$44:$B$84,"<="&EOMONTH(DATE(YEAR(J4),1,1),0))

Modified Formula for F8 (February) #

=SUMIFS(D$44:$D$84,$B$44:$B$84,">="&DATE(YEAR(J4),2,1),$B$44:$B$84,"<="&EOMONTH(DATE(YEAR(J4),2,1),0))

How It Works (Breakdown) #

• YEAR(J4): Extracts the year from cell J4. If J4 contains a full date (e.g., 2024-6-15), this still grabs 2024—no need to limit J4 to just a year.
• DATE(YEAR(J4),1,1): Generates the first day of January for the year in J4, replacing your hardcoded 2018-1-01.
• EOMONTH(...,0): Calculates the last day of the month for the given start date. This automatically handles leap years (February 29) and months with 30/31 days, so you never have to worry about incorrect end dates again.

Pro Tip: Auto-Fill for All Months #

If your columns correspond to months (e.g., E=Jan, F=Feb, etc.), you can make the formula even more dynamic by referencing the month from a header cell (say, E7 has "1" or "January"). This lets you right-fill the formula across all months without editing each one:

=SUMIFS(D$44:$D$84,$B$44:$B$84,">="&DATE(YEAR(J4),MONTH(E$7),1),$B$44:$B$84,"<="&EOMONTH(DATE(YEAR(J4),MONTH(E$7),1),0))

Just make sure E$7 (and F$7, G$7, etc.) contains a value that MONTH() can recognize (either a number 1-12 or a date like 2024-1-1).

Final Result #

Now, whenever you need to update the year, just change the date/year in J4—all your SUMIFS calculations will update instantly. No more manual formula edits, no more date errors!

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