Google Sheets条件格式：对比单列数值与拆分求和后的单元格值

Got it, let's work through this problem step by step to get your conditional formatting set up correctly. Here's exactly what you need to do:

Step 1: Build the Core Comparison Formula #

The goal is to split the underscore-separated values in column G, sum them up, and check if that total doesn't match the invoiced quantity in column D. Use this formula as your conditional rule:

=SUM(SPLIT(G1, "_")) <> D1

If your data starts at row 2 (with row 1 as headers), adjust the row numbers to match your dataset:

=SUM(SPLIT(G2, "_")) <> D2

Breakdown of the Formula:

• SPLIT(G1, "_"): Takes the value in G1 (like 15_25_10) and splits it into an array of individual numbers (15, 25, 10).
• SUM(...): Adds all the split values together to calculate the total shipped quantity.
• <> D1: Checks if this total is not equal to the invoiced quantity in D1. If true, the conditional format will trigger.

Step 2: Apply the Conditional Formatting #

1. Select the range of cells you want to highlight. For example, if your data runs from row 2 to row 100, select D2:G100—this will highlight both the D and G cells in any row where quantities don't match.
2. Go to Format > Conditional formatting from the top menu bar.
3. In the right-side panel, under "Format rules", pick Custom formula is from the dropdown menu.
4. Paste the formula you built into the input box.
5. Choose your preferred highlight style (e.g., a light orange fill to make mismatches easy to spot).
6. Click Done to save the rule.

Optional: Add Error Handling #

If column G might have empty cells or invalid formatting (values that aren't underscore-separated numbers), use this modified formula to handle errors gracefully—it treats invalid entries as a total of 0, ensuring they get highlighted to flag data issues:

=IFERROR(SUM(SPLIT(G2, "_")), 0) <> D2

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