Excel Hacks in MAP and SUMIF Function

🚀 Excel Tip: MAP Without LAMBDA

Did you know Excel’s MAP function doesn’t always require a LAMBDA?

If you pass a built‑in function directly, Excel auto‑wraps it behind the scenes — but only if the order of arrays matches the order of the function’s arguments.

=MAP(A3:A6,B3:B6,C3:C6,MID)

This works because:

A3:A6 → goes into MID’s text argument

B3:B6 → goes into start_num

C3:C6 → goes into num_chars

Excel interprets MID as if it were wrapped in:

LAMBDA(text,start,num_chars,MID(text,start,num_chars))

• If arrays are out of order, results break.

• If you supply more arrays than the function expects, you’ll get errors.

• If fewer, Excel won’t know how to map them.

🔥 SUMIF with Multi‑Column Ranges:

Most Excel users assume SUMIF only works with single columns. But here’s the hidden trick:

if your criteria_range and sum_range are the same multi‑column size, SUMIF can evaluate them together and spill results dynamically.

=SUMIF(A3:B12,UNIQUE(TOCOL(A3:B12)),C3:D12)

SUMIF isn’t restricted to one column anymore. With dynamic arrays, it can handle multi‑column ranges in both the criteria and sum arguments — provided they’re the same shape.