如何用起始日期范围与单个结束日期计算NETWORKDAYS并用于SUMIFS条件？

Great question! Let's break down the issue with your current formula and walk through the correct approaches to get the results you need for the M4:U4 range.

The Problem with Your Current Formula #

Your existing SUMIFS formula tries to use an array calculation (NETWORKDAYS($G:$G,N3)/30) as one of the conditions, but SUMIFS doesn't natively support array-based criteria. This means it won't evaluate the NETWORKDAYS check row-by-row like you need, leading to incorrect results.

Solution 1: Excel 365/2021 (Dynamic Array Compatible) #

If you're using a modern Excel version with dynamic array support, use SUM + FILTER for a clean, readable solution. This will automatically handle row-by-row evaluation:

=SUM(FILTER($J:$J, ($G:$G < N3) * ($H:$H >= N3) * ($I:$I >= NETWORKDAYS($G:$G, N3)/30), 0))

How it works:

• ($G:$G < N3): Checks if the project start date is earlier than the date in the current column (e.g., N3)
• ($H:$H >= N3): Ensures the project end date is on or after the column's date
• ($I:$I >= NETWORKDAYS($G:$G, N3)/30): Compares the project's duration (in months) to the approximate month count of working days between the project start date and the column's date
• FILTER returns all matching values from column J, and SUM adds them up. The 0 at the end ensures it returns 0 if no matches are found.

You can simply drag this formula across M4:U4, and it will automatically adjust the cell reference (like N3 → M3, O3, etc.) for each column.

Solution 2: Older Excel Versions (No Dynamic Arrays) #

For older Excel versions, use SUMPRODUCT instead—it natively handles array operations without needing to press Ctrl+Shift+Enter:

=SUMPRODUCT($J:$J * ($G:$G < N3) * ($H:$H >= N3) * ($I:$I >= NETWORKDAYS($G:$G, N3)/30))

How it works:

Each condition (($G:$G < N3), etc.) returns an array of TRUE/FALSE values, which Excel converts to 1/0 when multiplied. Multiplying these arrays together gives a 1 only where all conditions are met, and multiplying by column J adds up only the matching rows' hours.

Pro Tip for Performance #

Avoid using full column references like $G:$G—instead, use a specific range (e.g., $G$2:$G$1000) that matches your actual data. This reduces unnecessary calculations and speeds up your workbook.

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