请求协助编写含IF语句与计算逻辑的Excel公式

Let's build the exact formula you need, first let's recap your rules to make sure we're aligned:

Your Rules Recap #

• Condition 1: If Column A equals "E " (with trailing space) AND the first two characters of the corresponding account code are "17" (Navy), multiply Column C's value by $5.06
• Condition 2: If Column A equals "M " (with trailing space) AND the first two characters of the account code are "17" (Navy), multiply Column C's value by $39.99
• Condition 3: If Column A equals "M " AND the first character of the account code is "2" (e.g., Army's 21**...), multiply Column C's value by $39.99

Assumptions #

I'm assuming your account code is stored in Column B — if it's in a different column, just replace B2 in the formula with your actual column reference.

Formula Options #

Option 1: Nested IF (Works for all Excel versions)

Paste this into cell E2 and drag down to apply to all rows:

=IF(AND(A2="E ", LEFT(B2,2)="17"), C2*5.06, IF(OR(AND(A2="M ", LEFT(B2,2)="17"), AND(A2="M ", LEFT(B2,1)="2")), C2*39.99, ""))

Option 2: IFS Function (Cleaner, for Excel 2019/365+)

If you have a newer Excel version, use IFS for more readable logic:

=IFS(AND(A2="E ", LEFT(B2,2)="17"), C2*5.06, OR(AND(A2="M ", LEFT(B2,2)="17"), AND(A2="M ", LEFT(B2,1)="2")), C2*39.99, TRUE, "")

Breakdown of the Formula #

• LEFT(B2,2): Extracts the first 2 characters of the account code to check for Navy's "17" prefix
• LEFT(B2,1): Extracts just the first character to check for the "2" prefix (e.g., Army's 21**)
• AND(...): Ensures both conditions in the pair are true (e.g., A is "E " AND account starts with 17)
• OR(...): Triggers if either of the two M-related conditions are met
• The final "" returns an empty cell if none of the conditions match — you can replace this with 0 or a custom message like "No Match" if needed

Quick Notes #

• Double-check that Column A's values exactly match "E " and "M " (including the trailing space). If your actual data doesn't have the space, remove it from the formula (e.g., change "E " to "E").
• If you ever need to update the multiplier values (like $5.06 or $39.99), you can either edit the formula directly or store those values in a separate range (e.g., a lookup table) and reference them for easier maintenance.

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