Collaborative Roles, Proposal Staging, Sign-Off Gates & Team Handoffs

Who this is for

Stage 8: Real-Time Collaboration, Review Gates & AI Workflows — Designed for learners mastering input vs formula zoning & least-privilege sharing roles, suggester proposal staging, delta audits & approval toggles, @mention thread sla trackers & multi-department tab consolidation, version diffs, viewer landing tabs & unanimous pre-close sign-off gates through interactive spreadsheet examples.

What you will practise

• Compute Channel Revenue in D2:D5 as Units * Unit Price, then fill Contributor inputs B2:C5 with #dbeafe and formula cells D2:D5 with #f1f5f9.
• Map each collaborator code in B2:B6 to its role in C2:C6 via XLOOKUP (default "Viewer"), then count Direct-Write seats (Owner + Contributor) in F7 and Non-Direct seats in F8.
• Sum Baseline and Proposed forecasts in B6:C6, then compute rounded % Lift (1 decimal) across D2:D6 using =ROUND((C2 - B2) / B2 * 100, 1).
• Compute Signed Delta ($k) in D2:D6 as C2 - B2, then classify each line in E2:E6 as "UNCHANGED" when D2 = 0 and "CHANGED" otherwise.
• Compute Effective Budget in E2:E5 via =IF(D2 = "APPROVED", C2, B2), then calculate Total Effective Budget in E6 and Approved Net Lift over Baseline in E7.
• Flag ratio errors in C2:C5 with =IF(ISERROR(B2 / A2), 1, 0), then set Share Status in F5 to "READY_TO_SHARE" only when F4 = 0 AND F2 = F3 (otherwise "HOLD").
• Count OPEN threads assigned to "@elena" in G4 and count Overdue OPEN threads (Age Days > $G$2) in G5 using COUNTIFS.
• On Consolidated, sum SalesInput!B2:B5 and OpsInput!B2:B5 into B2:B5, then calculate the Annual Total in B6 with SUM.

Your learning path

1. Cell Equals, Count, or Sum One of Many Values (Allowlist COUNTIF + SUMPRODUCT)

   Replace brittle 10-way OR chains with a range-driven COUNTIF allowlist flag to test, count, and sum rows matching any value in an editable lookup list.

2. Count & Sum Cells Ending With or Starting With Specific Text (COUNTIF / SUMIF)

   Match SKU suffixes ("*" & suffix) and regional prefixes (prefix & "*") dynamically with COUNTIF and SUMIF while preventing blank-affix wildcard traps.

3. SUMIF & COUNTIF when cells contain a literal asterisk (*) or question mark (?)

   Escape wildcard characters with a tilde ("*~**") so SUMIF and COUNTIF match literal asterisks, question marks, or tildes instead of every text row.

4. COUNTIF & SUMIF Across Non-Contiguous Ranges (Additive & VSTACK)

   Count matching rows and sum quantities from two separate blocks, with literal criteria and a dynamic-array alternative.

5. Calculate Percent Absent & Attendance Rate Ignoring Unmarked Days

   Compute exact student or employee absence percentage COUNTIF(B2:G2, "A") / COUNTA(B2:G2) and present/excused attendance rate while ignoring blank unrecorded future sessions.

6. SUMIFS with Multiple Criteria and OR Logic Without Double-Counting Rows

   Distinguish safe same-column OR addition in SUMIFS from overlapping cross-column OR conditions where naive SUMIF + SUMIF double-counts rows that satisfy both rules.

7. Lookup and sum an entire column or row dynamically (SUM + XLOOKUP)

   Match a quarter, month, or scenario header horizontally and SUM, AVERAGE, or MAX the entire matched column in one readable formula.

8. Summarize Survey Results by Group: Top-2-Box % & Net Favorability (COUNTIFS)

   Aggregate 1-to-5 CSAT or employee-pulse survey responses by department into Top-2-Box satisfaction percentage (scores 4 & 5) and Net Favorability (% Top-2 minus % Bottom-2) using COUNTIFS.