*Stopped at Sumifs

1) Relative reference: B4

Meaning: “Use the cell that is this many rows/cols away from me.”

If you write =D8*E3 and copy it down one row, Excel will change it to:

• =D9*E4

Because both references are relative, Excel assumes you want both to move.

✅ Great when each row has its own inputs.

❌ Bad when you want to always point to the same discount rate cell.

2) Absolute reference: $B$4

Meaning: “Always point to exactly B4—never move.”

If you write =$D$8*$E$3 and copy anywhere, it stays:

• =$D$8*$E$3

✅ Great when you want a single “constant” cell (like a tax rate in one cell).

❌ Not ideal here if you want D8 to change per row.

3) Mixed reference (lock column): $B4

Meaning: “Column B is fixed, row can change.”

If you copy $B4:

• copy down → $B5 (row changes)
• copy right → $B4 (column does NOT change)

✅ Useful when you always want to use the same column, but different rows.

4) Mixed reference (lock row): B$4

Meaning: “Row 4 is fixed, column can change.”

If you copy B$4:

• copy down → B$4 (row does NOT change)
• copy right → C$4 (column changes)

✅ Useful when you always want to use the same row, but possibly different columns.

Quick rule of thumb

• Copying down breaks the row unless you lock it with $
• Copying right breaks the column unless you lock it with $
• Put $ in front of whatever you want to freeze

Yes: SUMIFS criteria are “ANDed” together — a row is included only if it meets all criteria pairs you provide.

The “IFS” family (same logic, different output)

SUMIFS

Adds up numbers for rows that meet all criteria.

Example: total quantity of blue chairs

=SUMIFS(D:D, A:A, "chair", B:B, "blue")

COUNTIFS

Counts how many rows meet all criteria.

Example: number of rows that are blue chairs

=COUNTIFS(A:A, "chair", B:B, "blue")

AVERAGEIFS

Averages numbers for rows that meet all criteria.

Example: average cost per item for blue chairs

=AVERAGEIFS(E:E, A:A, "chair", B:B, "blue")

Important rules (that trip people up)

1) Each criteria_range must match the sum_range size/shape

If sum_range is D4:D30, then every criteria range must also be something4:something30 (same number of rows).

2) Criteria are evaluated row-by-row

It’s not “sequential” like step 1 then step 2; it’s more like:

For each row: if (criterion1 true) AND (criterion2 true) AND … → include it.

3) Criteria can be expressions

You can do:

• ">=5", "<10"
• "<>chair" (not equal)
• use cell refs: A:A, G1 (where G1 contains "chair")

Example (sum quantity where item=chair and quantity>=5):

=SUMIFS(D:D, A:A, "chair", D:D, ">=5")

What if you want OR logic?

SUMIFS does not do OR inside one function across different criteria pairs. You have common options:

Option A: Add multiple SUMIFS (classic OR)

Chair OR desk, blue only

=SUMIFS(D:D, A:A, "chair", B:B, "blue")
 +SUMIFS(D:D, A:A, "desk",  B:B, "blue")

Option B: Array constant (works in modern Excel / 365)

Chair OR desk, blue only

=SUM(SUMIFS(D:D, A:A, {"chair","desk"}, B:B, "blue"))

Option C: OR on the same field via wildcards or pattern logic

If you need pattern matching (text contains), you can use * wildcards:

• "blu*" matches “blue”, “bluish”, etc.
• "*air*" matches “chair” (and anything containing air)

Example:

=SUMIFS(D:D, B:B, "blu*")

This is a classic hurdle. Using VLOOKUP and HLOOKUP separately is static because you have to manually count which column or row you want (e.g., telling Excel "look in column 3"). If you insert a column later, your whole model breaks.

To make this dynamic (looking up both the correct row and the correct column automatically), finance professionals generally abandon VLOOKUP/HLOOKUP in favor of INDEX & MATCH.

Here is how to solve your specific problem, followed by your requested 10-minute crash course.

Part 1: The Solution (2D Dynamic Lookup)

You want to find a value based on the Item (Vertical) and the Header (Horizontal).

The Formula: INDEX + MATCH + MATCH

Think of INDEX as a GPS. You just give it a map (the data), a latitude (Row number), and a longitude (Column number). We use MATCH to find those coordinates automatically.

The Syntax:

=INDEX(Data_Area, MATCH(Row_Item, Row_List, 0), MATCH(Col_Header, Header_Row, 0))

Applied to your "Office Sales" example:

Let's say you want to find 2019 Sales for Office B.

• Data Area: The numbers inside your table (e.g., B13:C18).
• Row Item: "Office B"
• Col Header: "2019 Sales"

Excel

=INDEX(B13:C18, MATCH("Office B", A13:A18, 0), MATCH("2019 Sales", B12:C12, 0))

Why this works:

1. First MATCH looks for "Office B" in the list of offices and says "That is Row 2".
2. Second MATCH looks for "2019 Sales" in the header row and says "That is Column 2".
3. INDEX looks at the data grid and says "Go to Row 2, Column 2" and returns 4382.

Note: If you are on Excel 365, you can also use XLOOKUP nested inside another XLOOKUP, but INDEX/MATCH remains the industry standard for modeling.

Part 2: Excel 101 for Finance & Management (10-Minute Digest)

Finance isn't about knowing every formula; it's about speed, auditing, and aggregation. Here are the 5 pillars you need to master.

1. Navigation (The "No Mouse" Rule)

Mouse usage slows you down. Master these to fly through spreadsheets.

• Ctrl + Arrows: Jump to the edge of a data region.
• Ctrl + Shift + Arrows: Highlight data from where you are to the edge.
• F2: Enter "Edit Mode" inside a cell (stops you from accidentally overwriting formulas).
• F4: The most important key.
• While editing a formula: It "Locks" cells (toggles between A1, $A$1, A$1, $A1). Essential for dragging formulas.
• While formatting: It repeats your last action (e.g., if you highlight a cell yellow, hitting F4 on another cell makes it yellow).

2. Aggregation: SUMIFS (Better than Pivot Tables for Modeling)

Managers ask: "How much did we sell in Q3 for Product X?"

Don't use SUMIF. Always use SUMIFS (plural), even for a single criteria. It allows you to add more criteria later without rewriting the formula.

Syntax:

=SUMIFS(Sum_Range, Criteria_Range1, Criteria1, Criteria_Range2, Criteria2...)

Example: Sum the Sales Column IF Region is "West" AND Date is ">1/1/2023".

3. Logic: Nested IF and IFS

Financial models require decision trees.

• Basic: =IF(Logic_Test, Value_if_True, Value_if_False)
• Finance Tip: Use IFERROR to clean up your sheets.
• Instead of #DIV/0! showing up when sales are zero, use: =IFERROR(A1/B1, 0).

4. Data Hygiene: "Paste Special"

Never copy-paste straight from a source/website into your model. You will bring in unwanted formatting that bloats the file.

• Shortcut: Ctrl + C (Copy) Alt + E + S + V (Paste Special: Values).

• This strips all formatting and leaves just the raw data.

5. The "Sanity Check" (Trace Precedents)

When you inherit a spreadsheet from someone else, you need to know where the numbers come from.

• Shortcut: Alt + M + P (Trace Precedents).
• This draws blue arrows showing exactly which cells feed into the current cell.
• Alt + M + A removes the arrows.

Summary Cheat Sheet

Would you like me to generate a practice dataset table so you can try writing an INDEX/MATCH formula right now?