Conditional Calculations and Logical Functions (IF, COUNTIF, SUMIF, etc.)

1. When you need to analyze only data that meets specific conditions

• Example: Calculating the total sales for a specific product category.

2. When you need to verify data errors

• Example: Checking if the payment amount is 0 or contains incorrect values.

Solutions:

1. IF Function: Returning values based on conditions

Problem: Marking sales data as "Excellent" if the sales quantity exceeds 100.

Usage:

=IF(B2>=100, "Excellent", "Average")

2. COUNTIF Function: Counting data that meets specific conditions

Problem: Counting the number of orders placed in a specific region (e.g., "Seoul").

Usage:

=COUNTIF(C1:C100, "Seoul")

3. SUMIF Function: Summing data that meets specific conditions

Problem: Calculating the total sales for a specific category (e.g., "Electronics").

Usage:

=SUMIF(D1:D100, "Electronics", E1:E100)

4. IFERROR Function: Handling errors

Problem: Managing errors gracefully when data is missing or a formula is incorrect.

Usage:

=IFERROR(A1/B1, "Error")

Additional Functions:

IFS Function

Example:

=IFS(A1>90, "Excellent", A1>50, "Average", TRUE, "Needs Improvement")

CHOOSE Function

Example:

=CHOOSE(2, "Apple", "Banana", "Grape")  // Result: Banana

AND, OR Functions

Example:

=AND(A1>0, B1<100)  // Returns TRUE or FALSE