How do I total a change order log by approved and pending status and tie it to the pay app contract sum with AI?

Read this change order log [upload the CSV export or the change order PDFs] and build a COLog table with one row per change order: CO number, description, amount (deducts negative), status and executed date, copied exactly. Write NOT SHOWN instead of guessing. List any status that is not one of Approved, Pending, Rejected or Void. Do not change a status and do not judge whether any change is owed.

EXAMPLE DATA ONLY. Here is my COLog tab [paste the example rows], my original contract sum [type yours] and pay app line 2 and line 3 [type yours]. Write Excel formulas (or a step-by-step Copilot plan) that total approved and pending separately with SUMIFS, exclude rejected and void, build the revised and pending revised contract sum, and flag: nonstandard status, approved with no executed date, duplicate CO numbers, numbering gaps, line 2 different from the approved total (and which pending changes add up to the difference), and line 3 different from original plus approved. Use only my data.

COLog tab (example, CSV)
CO,Description,Amount,Status,ExecutedDate
CO-001,Added floor drains at mechanical room,18400,Approved,2026-08-14
CO-002,Owner-requested door hardware upgrade,7250,Approved,2026-08-29
CO-003,Unforeseen rock at footing excavation,42000,Pending,
CO-004,Delete entry canopy,-15600,Approved,2026-09-10
CO-005,Revised light fixture package,9800,Rejected,
CO-006,Additional firestopping at shafts,3150,Void,
CO-008,Relocate roof drains,6900,Pending,
CO-009,Added blocking for wall-mounted displays,4300,Approved,
CO-010,Extra hose bibb at loading area,2500,Appr,2026-10-02
Typed from the executed agreement: original contract sum 1,250,000
Typed from the current pay app: line 2 net change by change orders 56,350; line 3 contract sum to date 1,306,350

EXAMPLE DATA ONLY (not from a real project). Original contract sum 1,250,000. COLog tab: nine change orders with number, description, amount, status (Approved, Pending, Rejected, Void and one typed as Appr) and executed date. Pay app line 2 is 56,350 and line 3 is 1,306,350. Write Excel formulas (or a step-by-step Copilot plan) that total approved and pending separately, exclude rejected and void, build the revised and pending revised contract sum, and flag nonstandard statuses, approved with no executed date, duplicate numbers, numbering gaps, and pay app line 2 or line 3 that does not match the approved log. Use only my data; do not decide whether any change is owed.

Expected results on this example (output of the script below on these rows)
CO-009 APPROVED BUT NO EXECUTED DATE → confirm it is signed
CO-010 NONSTANDARD STATUS 'Appr' → not counted; fix the status
CO-007 NUMBER GAP → missing from log, or voided without a row
Approved 14,350 | Pending 48,900 (rejected 9,800 and void 3,150 left out of every total)
Revised contract (approved only) 1,264,350 | Pending revised 1,313,250
PAY APP LINE 2 56,350 vs approved log 14,350 (+42,000)
  difference equals pending: CO-003 → a pending change is being billed as if approved
LINE 3 1,306,350 vs original + approved 1,264,350 (+42,000)

Suggested Excel logic (write against your own sheet layout)
- Clean status in a helper column: PROPER(TRIM([@Status])); flag with COUNTIF(StatusList, [@Clean])=0.
- Approved total: SUMIFS(COLog[Amount], COLog[Clean], "Approved"); pending the same with "Pending". Never use a plain SUM of the column.
- Approved with no date: AND([@Clean]="Approved", [@ExecutedDate]="").
- Duplicates: COUNTIF(COLog[CO], [@CO])>1. Gaps: compare the numeric part against SEQUENCE(MAX(...)).
- Pay app: Line2 - ApprovedTotal and Line3 - (Original + ApprovedTotal), flagged when not zero.

Same check as a short Python script (our own code; reads the CSV above saved as co_log.csv)
import csv
from itertools import combinations
ORIGINAL = 1250000     # original contract sum, typed from the executed agreement
LINE2 = 56350          # net change by change orders, typed from the pay app
LINE3 = 1306350        # contract sum to date, typed from the pay app
ALLOWED = {"APPROVED", "PENDING", "REJECTED", "VOID"}
rows = list(csv.DictReader(open("co_log.csv")))
tot = {"APPROVED": 0, "PENDING": 0}
seen, nums, pending = {}, [], []
for r in rows:
    co, st, amt = r["CO"].strip().upper(), r["Status"].strip().upper(), float(r["Amount"])
    if co in seen: print(co, "DUPLICATE CO NUMBER")
    seen[co] = r
    if co.split("-")[-1].isdigit(): nums.append(int(co.split("-")[-1]))
    if st not in ALLOWED:
        print(co, f"NONSTANDARD STATUS '{r['Status']}' → not counted; fix the status"); continue
    if st == "APPROVED" and not r["ExecutedDate"].strip():
        print(co, "APPROVED BUT NO EXECUTED DATE → confirm it is signed")
    if st in tot: tot[st] += amt
    if st == "PENDING": pending.append((co, amt))
for n in range(min(nums), max(nums) + 1):
    if n not in nums: print(f"CO-{n:03d}", "NUMBER GAP → missing from log, or voided without a row")
appr = tot["APPROVED"]
print(f"Approved {appr:,.0f} | Pending {tot['PENDING']:,.0f}")
print(f"Revised contract (approved only) {ORIGINAL+appr:,.0f} | Pending revised {ORIGINAL+appr+tot['PENDING']:,.0f}")
diff = LINE2 - appr
if abs(diff) > 0.5:
    print(f"PAY APP LINE 2 {LINE2:,.0f} vs approved log {appr:,.0f} ({diff:+,.0f})")
    for k in range(1, len(pending) + 1):
        for c in combinations(pending, k):
            if abs(sum(a for _, a in c) - diff) < 0.5:
                print("  difference equals pending:", ", ".join(x for x, _ in c))
if abs(LINE3 - (ORIGINAL + LINE2)) > 0.5:
    print(f"LINE 3 {LINE3:,.0f} != Line 1 + Line 2 {ORIGINAL+LINE2:,.0f}")
if abs(LINE3 - (ORIGINAL + appr)) > 0.5:
    print(f"LINE 3 {LINE3:,.0f} vs original + approved {ORIGINAL+appr:,.0f} ({LINE3-ORIGINAL-appr:+,.0f})")
print("Only flags for review; whether a change is owed stays with the project manager under the contract.")