17. Interview Questions and Answers
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.
Ravindra Bagale's Tip
Many students can name a function but can't write its syntax – and get stuck in a live test. You should be able to write these five formulas even on paper: SUMIFS, XLOOKUP, INDEX-MATCH, IF-AND and TEXT. Practise by writing one formula by hand every day.
Ravindra Bagale's Tip – मराठी
बरेच students function चं नाव सांगतात पण syntax लिहिता येत नाही – आणि live test मध्ये अडकतात. SUMIFS, XLOOKUP, INDEX-MATCH, IF-AND आणि TEXT हे पाच formulas कागदावर पण लिहिता आले पाहिजेत. रोज एक formula हाताने लिहून practice करा.
Ravindra Bagale's Tip – हिंदी
बहुत से students function का नाम तो बता देते हैं पर syntax नहीं लिख पाते – और live test में अटक जाते हैं. SUMIFS, XLOOKUP, INDEX-MATCH, IF-AND और TEXT – ये पाँच formulas काग़ज़ पर भी लिखने आने चाहिए. रोज़ एक formula हाथ से लिखकर practice करो.
Loading practice tasks...
- Total quantity (Easy): Add up the "Quantity" column.
- Average unit price (Easy): Average of the Unit price ($) column, rounded to 2 decimals.
- Rows in Website (Easy): How many rows have Channel "Website"?
- Largest quantity (Easy): The largest value in the "Quantity" column.
- Quantity for Website (Medium): Total of the Quantity column where Channel is "Website".
- Website in Austin (Medium): Total of the Quantity column where Channel is "Website" and City is "Austin".
- Best in Website (Medium): Largest value in the "Unit price ($)" column where "Channel" is "Website".
- Value of row 2 (Medium): Multiply the first-row Quantity by the first-row Unit price ($).
- Look up Priya Kulkarni (Medium): The Quantity for "Priya Kulkarni" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Quantity" times "Unit price ($)" for all rows.
- Share of Website (Hard): Share of the "Quantity" column that is "Website", rounded to 3 decimals.
- Share of the total "Unit price ($)" with a locked range (Hard): Find what share of the total "Unit price ($)" the first row has. Round it to 3 decimals.
- Above average? (Hard): "Above" if the "Unit price ($)" value in the first row is above the average of the "Unit price ($)" column, else "Below".
Worked example — functions and formulas
Why
Rohan Patil in Pune and Anna Kowalski abroad both need a clear, repeatable way to use functions and formulas on a NovaCraft Tools sheet without breaking totals.
What
Functions And Formulas is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for functions and formulas.
- Enter a small made-up value for Rohan Patil using $ where money appears.
- Add a second value for Anna Kowalski using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once functions and formulas is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
