如何用SUMIFS函数按指定日期前后7天范围汇总旅行发票？

Got it, let's solve this exactly for your travel invoice scenario—since folks can have multiple trips in a month (like start and end of the month), we need to lock in specific 7-day windows instead of just summing the whole month. Here's how to build the perfect SUMIFS formula:

Core Formula Template #

This combines your custom criteria (like traveler name, expense type) with the critical 7-day date filter for column I:

=SUMIFS(
    $B:$B,                  // Column with invoice totals to sum
    $A:$A, "John Doe",      // First criteria: match traveler name (adjust range/criteria as needed)
    // Add more criteria pairs here (e.g., category, approval status)
    $I:$I, ">="&$K$2-7,     // Start of window: 7 days BEFORE the target date in K2
    $I:$I, "<="&$K$2+7      // End of window: 7 days AFTER the target date in K2
)

Breakdown of the Date Logic #

The magic here is the date range:

• $K$2 is your reference date (pick a date that falls within the specific trip you want to sum—like the trip's start date)
• >=&$K$2-7 grabs all dates 7 days before or on the reference date
• <=&$K$2+7 grabs all dates 7 days after or on the reference date
• Together, this creates a 15-day window (7 days before + reference day + 7 days after) that perfectly encapsulates a single trip, even if it crosses month boundaries.

Example for Multiple Trips #

Say "Jane Smith" has two trips:

1. Early January (around 2024-01-03)
2. Late January (around 2024-01-29)

• Put 2024-01-03 in cell K2, use the formula above to sum her early-month trip invoices
• Put 2024-01-29 in cell K3, copy the formula and update $K$2 to $K$3—now you have separate totals for each trip, no overlap.

Pro Tips to Avoid Headaches #

• Lock Ranges: Use absolute references (like $B:$B instead of B:B) if you plan to copy the formula to other cells—this keeps your criteria ranges from shifting accidentally.
• Check Date Formats: Make sure column I is set to Excel's Date format (not text). If dates are stored as text, the comparison operators (>=, <=) won't work correctly.
• Adjust the Window: If you don't want to include the reference date itself, swap >= for > and <= for <. But for most travel scenarios, including the reference date makes sense to capture the full trip.

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