Advanced Excel Interview Questions Set 9

Intermediate Level

Explain the difference between SUMIF() and SUMIFS() functions in excel.

- SUMIF(): Adds the values based on a single condition.

- Syntax: SUMIF(range, criteria, [sum_range])

- Example: To sum all sales where the region is "East": =SUMIF(A2:A10, "East", B2:B10)

This adds up values in B2:B10 where the corresponding value in A2:A10 is "East".

- SUMIFS(): Adds values based on multiple conditions.

- Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

- Example: To sum sales where the region is "East" and the sales are greater than 100: =SUMIFS(B2:B10, A2:A10, "East", B2:B10, ">100")

This sums values in B2:B10 where A2:A10 is "East" and the value in B2:B10 is greater than 100.

- Key Difference: SUMIF() handles only one condition, while SUMIFS() can handle multiple.

Explain the difference between COUNT(), COUNTA() and COUNTBLANK() functions in excel.

- COUNT(): Counts only numerical values in a range.

- Syntax: COUNT(range)

- Example: If you want to count numbers in the range A1:A10:

=COUNT(A1:A10) counts only the cells containing numbers.

- COUNTA(): Counts all non-empty cells (both numbers and text).

- Syntax: COUNTA(range)

- Example: =COUNTA(A1:A10) counts cells containing numbers, text, or formulas.

- COUNTBLANK(): Counts the number of blank cells in a range.

- Syntax: COUNTBLANK(range)

- Example: =COUNTBLANK(A1:A10) counts empty cells in the range.

- Key Difference: COUNT() counts only numbers, COUNTA() counts all non-empty cells, and COUNTBLANK() counts only empty cells.

Explain the difference between IF() and IFERROR() functions in excel.

- IF(): Checks if a condition is met and returns one value if TRUE and another if FALSE.

- Syntax: IF(logical_test, [value_if_true], [value_if_false])

- Example: =IF(A1 > 100, "High", "Low") returns "High" if A1 is greater than 100, otherwise it returns "Low".

- IFERROR(): Returns a specified value if a formula results in an error; otherwise, it returns the result of the formula.

- Syntax: IFERROR(value, [value_if_error])

- Example: =IFERROR(A1/B1, "Error") returns the result of A1/B1, but if it causes an error (like division by zero), it returns "Error".

- Key Difference: IF() checks a logical condition, while IFERROR() catches and handles errors in a formula.