The middle of a spreadsheet screen moves from single totals to lists and lookups. Expect to be asked for a total between two dates the interviewer types in cells, a value from a grid by row and column, a filtered or sorted list, and a column calculated with one formula.
What interviewers listen for at this level:
- Inputs in cells. Dates, names and thresholds belong in cells your formula refers to, so the answer follows when they change them.
- The right shape. A list should spill from one cell; a filled column should be one formula copied down, or one ARRAYFORMULA.
- Boundaries. "Between February 1 and March 31" includes both ends.
Any approach that gives the right answer is accepted. QUERY, FILTER and the lookup functions often solve the same question; pick the one you can explain.
=SUMIFS(G2:G17, A2:A17, ">="&K1, A2:A17, "<="&K2)
=QUERY(A1:G17, "select B, sum(G) group by B order by sum(G) desc", 1)
Revenue between two dates in cells; revenue per rep, highest first.