如何在Excel中按月份逻辑求和？跨工作表SUMIF公式问题

Hey there! Let's get that formula working for you. First, let's clarify the table structures we're working with based on your description, then fix the formula step by step.

Understanding Your Sheets #

• Sheet1:
  • Column A = IDs (Q1, Z1)
  • Column B = Names
  • Row 1 = Months (1月, 2月, etc. as headers)
  • You need cells like C2, C3, D2, etc. to calculate the total amount for each ID+month pair from Sheet2.
• Sheet2:
  • Column A = IDs (Q1, Z1)
  • Row 1 = Months (as column headers, with corresponding amounts in the rows below)

What Was Wrong With Your Original Formula #

Your initial attempt used incorrect syntax for SUMIF — this function only handles a single condition, and you were mixing invalid select-style syntax that Excel doesn't recognize. Since you need to match two conditions (ID and month), we'll use SUMIFS (the multi-condition version of SUMIF) instead.

The Correct Formula #

In Sheet1's cell C2 (for Q1's 1月 total amount), enter this formula:

=SUMIFS(Sheet2!B:B, Sheet2!A:A, $A2, Sheet2!$1:$1, C$1)

Let's break down what each part does:

• Sheet2!B:B: The column in Sheet2 where 1月 amounts are stored (adjust this if your 1月 column is different, e.g., Column C)
• Sheet2!A:A: The column in Sheet2 containing IDs (we'll match this to Sheet1's ID in Column A)
• $A2: Locks the column (so dragging down keeps Column A) while letting the row change (to Z1 in row 3, etc.)
• Sheet2!$1:$1: Locks the row (Sheet2's month headers) to match against Sheet1's month headers
• C$1: Locks the row (Sheet1's month header row) while letting the column change (to D$1 for 2月, etc.)

How to Fill the Rest of the Cells #

1. Hover over the bottom-right corner of cell C2 until you see a small cross (fill handle).
2. Drag it right to cover all your month columns — the formula will automatically update to match each month header.
3. Then drag the filled row down to cover Z1's row — it'll update to match the ID in Column A for each row.

If Your Months Are Date-Formatted #

If Sheet2's month headers are actual dates (e.g., 1/1/2024 instead of text "1月"), adjust the formula to convert the dates to match your Sheet1 text format:

=SUMIFS(Sheet2!B:B, Sheet2!A:A, $A2, TEXT(Sheet2!$1:$1, "m月"), C$1)

This will convert Sheet2's date headers to "1月", "2月" etc. to match Sheet1's text headers.

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