SUMIFS函数文本与日期对比报错：数组参数大小不一致问题排查

Got it, let's break down why your formula is failing and get you the correct total revenue per employee as you need.

Root Causes of the Error #

Your formula hits this error for a few key reasons, plus a potential typo mix-up:

1. SUMIFS won’t accept calculated arrays as criteria ranges
   SUMIFS requires every criteria range to be a physical range of cells (like P14:P1013), not a dynamically generated array from a function like TEXT(N14:N1013,"MMMM"). When you pass that calculated array as a criteria range, Excel can’t align it properly with the other ranges, triggering the size mismatch error.

2. Incorrect SUMIFS parameter structure
   SUMIFS follows a strict syntax: after the sum range, it needs pairs of [criteria range, criteria value]. Looking at your formula, you’ve mixed up the order of these pairs, and it seems you used the wrong columns (e.g., U14:U1013 instead of W14:W1013, T14:T1013 instead of V14:V1013) based on your stated requirements.

3. Possible range size mismatch
   Even if the syntax was right, if any of your ranges (like U14:U1013 or T14:T1013) don’t have the exact same number of rows as your sum range R14:R1013, Excel will throw this size error.

Correct Solutions #

Option 1: Use a Helper Column (Simpler for Quick Setup) #

Since SUMIFS needs physical cell ranges for criteria, add a helper column to convert your dates in column N to month names:

• Pick a blank column (e.g., column X)
• In cell X14, enter: =TEXT(N14,"MMMM")
• Drag this formula down to X1013 to apply it to all rows

Now use this helper column in your SUMIFS formula, making sure all ranges match your sum range’s row count and reference the correct columns from your requirements:

=SUMIFS(R14:R1013, P14:P1013, W14:W1013, X14:X1013, V14:V1013)

• R14:R1013: Your revenue sum range
• P14:P1013: First criteria range (matches column W values)
• W14:W1013: First criteria value (matches column P values)
• X14:X1013: Second criteria range (month names from helper column)
• V14:V1013: Second criteria value (month names to match)

Option 2: Use SUMPRODUCT (No Helper Column Needed) #

If you don’t want to add extra columns, SUMPRODUCT handles array calculations natively, so it’s perfect for this scenario. This formula checks both conditions, multiplies boolean results (1 for true, 0 for false) by the revenue values, then sums only the valid entries:

=SUMPRODUCT((P14:P1013=W14:W1013)*(TEXT(N14:N1013,"MMMM")=V14:V1013)*R14:R1013)

• (P14:P1013=W14:W1013): Verifies column P matches column W for each row
• (TEXT(N14:N1013,"MMMM")=V14:V1013): Checks if the month name from column N matches column V
• The * operator converts TRUE/FALSE to 1/0, so only rows meeting both conditions contribute to the final sum

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