Google Sheets中利用IF THEN函数统计已成交订单的技术问询

Got it, let's sort this out for you! You need your "Closed Deal" column to automatically show the estimated quote only when the corresponding status is "Approved"—and $0 if it's "Denied" or "Pending". Here's how to implement this in both Excel and Google Sheets:

Excel Implementation #

Assuming your status is in column B (e.g., B2 is the first status cell) and your estimated quote is in column C (C2 is the matching quote), enter this formula in the first "Closed Deal" cell (say, D2):

=IF(B2="Approved", C2, 0)

Then drag the fill handle down to apply this formula to all rows in the column.

Extra Tip: Handle Case Insensitivity #

If there's a chance the status might be entered in lowercase (like "approved") or mixed case, use the UPPER function to make the check case-insensitive:

=IF(UPPER(B2)="APPROVED", C2, 0)

Finally, format the "Closed Deal" column as Currency (USD) to make the $0 and quotes display properly with dollar signs and decimal places.

Google Sheets Implementation #

The formula works almost identically in Google Sheets. Using the same column references (status in B2, quote in C2, Closed Deal in D2):

=IF(B2="Approved", C2, 0)

Drag the formula down to apply it to all rows. For case insensitivity, use the same UPPER trick as Excel:

=IF(UPPER(B2)="APPROVED", C2, 0)

To get the dollar format, select the "Closed Deal" column, go to Format > Number > Currency (USD).

That's it—this setup will automatically update the "Closed Deal" value whenever the status changes!

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