17.2 Functions and Formulas

Q9. Explain SUMIF vs SUMIFS.

SUMIF has one condition: =SUMIF(criteria_range, criteria, sum_range). SUMIFS supports many conditions and puts the sum range first: =SUMIFS(sum_range, criteria_range1, criteria1, …). I use SUMIFS everywhere for consistency, even with one condition.

Q10. How do you write an IF with several conditions?

Use AND/OR inside IF: =IF(AND(G2>=500,I2="Delivered"),"Big order",""). For several outcomes use nested IF or IFS (Excel 2019+): =IFS(G2>=500,"High",G2>=100,"Medium",TRUE,"Low"). SWITCH is cleaner when comparing one value against a list.

Q11. What does SUMPRODUCT do? Give an example.

It multiplies arrays element by element and sums the results. Weighted average delivery time: =SUMPRODUCT(H2:H11,F2:F11)/SUM(F2:F11). With conditions: =SUMPRODUCT((C2:C11="Pune")*(I2:I11="Delivered")*G2:G11).

Q12. How do IFERROR and IFNA differ?

IFERROR catches every error type (#N/A, #DIV/0!, #VALUE!…). IFNA catches only #N/A. For lookups I prefer IFNA or XLOOKUP's if_not_found so genuine formula mistakes (like #REF!) are not hidden.

Q13. Name the common Excel error values and their causes.

#N/A – lookup value not found. #DIV/0! – division by zero or blank. #VALUE! – wrong data type. #REF! – deleted cell reference. #NAME? – misspelled function or name. #NUM! – invalid number. #SPILL! – dynamic array cannot spill. #CALC! – for example FILTER with no results and no fallback.

Q14. Which text functions do you use for cleaning?

TRIM, CLEAN, PROPER/UPPER/LOWER, LEFT/RIGHT/MID, FIND/SEARCH, SUBSTITUTE, LEN, TEXT, VALUE, TEXTJOIN; in Microsoft 365, TEXTBEFORE, TEXTAFTER and TEXTSPLIT. Example: =PROPER(TRIM(C2)) turns " pune " into "Pune".

Q15. How do you extract the domain from an e-mail address?

=MID(A2,FIND("@",A2)+1,LEN(A2)), or in Microsoft 365 =TEXTAFTER(A2,"@"). For zoya@example.com both return example.com.

Q16. How do you calculate the difference between two dates in days, months or years?

Subtract for days (=C2-B2), use DATEDIF(start,end,"m") or "y" for complete months/years, NETWORKDAYS for working days, and EDATE/EOMONTH to move by months.

Q17. How do you calculate year-over-year or month-over-month growth?

=(Current-Previous)/Previous, formatted as %, wrapped in IFERROR for a zero base. In a PivotTable, use Show Values As › % Difference From with the previous month or year as the base item.

Q18. What is a running total and how do you create one?

A cumulative sum. With an expanding range: =SUM($G$2:G2) copied down. In a pivot: Show Values As › Running Total In › Date.

Q19. What is an array formula?

A formula that works on multiple values at once. Before Microsoft 365, it needed Ctrl + Shift + Enter (curly braces). In Microsoft 365, dynamic arrays calculate arrays natively and spill results, e.g. =UNIQUE(C2:C100).

Q20. What are volatile functions?

Functions that recalculate on every change anywhere in the workbook: NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT. Thousands of them slow a workbook, so I replace OFFSET/INDIRECT with INDEX or Tables where possible.