Criteria strings may begin with =, <>, <, <=, >, or >=. Numeric and boolean operands are parsed before text comparison. Plain text comparisons are case-insensitive. * matches zero or more characters, ? matches one character, and ~ escapes the next wildcard. Wildcards apply to equality/inequality text criteria. Criteria are parsed once per formula evaluation, not once per cell.
Shapes are exact row-by-column dimensions; equal cell counts with different dimensions do not match:
COUNTIF(criteria_range, criterion) accepts one matrix and one scalar criterion.
COUNTIFS(criteria_range1, criterion1, …) requires one or more range/criterion pairs. Every criteria range must have exactly the shape of the first.
SUMIF(criteria_range, criterion, [value_range]) and AVERAGEIF(...) use criteria_range as the value range when the third argument is omitted. When supplied, value_range must have exactly the same shape; Sheetwrite does not implement Excel's top-left range extension.
SUMIFS(value_range, criteria_range1, criterion1, …), AVERAGEIFS, MAXIFS, and MINIFS require one or more pairs after the value range. Every criteria range must exactly match the value range.
A mismatch or malformed pair list returns #VALUE!. Matching result-range errors propagate. Text, booleans, and blanks in a numeric result range are ignored. A criteria sum with no numeric matches is 0; an average is #DIV/0!; MAXIFS/MINIFS return 0.
SUMPRODUCT and SUBTOTAL
SUMPRODUCT(array1, [array2, …]) requires at least one argument. Every argument must have the same exact row-by-column shape; a scalar is a 1×1 shape, and a dimensional mismatch returns #VALUE!. It multiplies corresponding cells and sums the products. Text, booleans, and blanks reached through ranges contribute zero; directly supplied numeric text and booleans use ordinary scalar numeric coercion. Errors propagate, and a non-finite product or total returns #NUM!.
SUBTOTAL(function_number, reference1, [reference2, …]) accepts codes 1–11: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, and VAR.P. It accepts up to 253 reference arguments and uses the corresponding aggregate's range coercion/error rules. Codes 101–111 are deliberately unsupported and return #VALUE! pending hidden-row provenance in formula range values. Nested-subtotal and filtered/hidden-row exclusion must not be inferred.
Financial functions
PV, FV, PMT, NPV, IRR, RATE, IPMT, and PPMT use binary64 arithmetic, fixed defaults from the generated signatures, and explicit domain errors. NPV discounts its first cash flow at period 1 and uses compensated summation. Range cash flows ignore text and blanks; errors propagate.
IRR requires at least one positive and one negative cash flow. IRR and RATE use the caller's guess (default 0.1), 14 deterministic bracket steps, at most 100 deterministic solve steps, fixed absolute/relative tolerances of 1e-12, and a finite transformed search domain. Invalid domains or failure to converge return #NUM!; no random seed, host locale, wall clock, or platform-specific iteration budget is used.
Lookup semantics
INDEX(range, row, [column], [area_num]) uses 1-based indices and returns one scalar. Zero/negative indices return #VALUE!; array-return row/column projections are not implemented. area_num selects an area of a union, such as =INDEX((A1:B2,D1:E2),1,1,2); an area_num after the last area returns #REF!.
MATCH(key, range, 0) is exact. Match type 1 returns the largest value less than or equal to the key from ascending data. -1 returns the smallest value greater than or equal to the key from descending data. Invalid ordering returns #N/A rather than a plausible wrong row.
VLOOKUP/HLOOKUP use exact matching when the final argument is false. The omitted/true mode uses correctly ordered approximate data and returns the largest value less than or equal to the key.
XLOOKUP supports exact, next-smaller (-1), next-larger (1), and wildcard (2) match modes; forward/reverse and ordered binary-search modes are accepted. if_not_found is optional. If it is omitted—including via an interior empty argument—the result is #N/A.
Lookup errors in the scanned range propagate. Approximate modes validate ordering and do not silently return a result from unsorted input.
const grid: Gridconstgrid:Gridconstgrid:GridThe imperative core grid this controller owns.API reference →Grid.store: StoreinterfaceGrid {store:Store;}interfaceGrid {store:Store;}Store.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultStore.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultStore.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultApply a low-level storage transaction. This bypasses Grid read-only checks and Grid undo/redo history. Use Grid.applyTransaction for normal host-driven edits. Queued and flushed at a barrier — never reentrant.API reference →Transaction.patches: DocumentOp[]interfaceTransaction {patches:DocumentOp[];}interfaceTransaction {patches:DocumentOp[];}Ordered document operations submitted as one store commit.API reference →addr: CellAddresslet addr:CellAddresslet addr:CellAddressAPI reference →CellAddress.sheet: stringinterfaceCellAddress {sheet:string;}interfaceCellAddress {sheet:string;}Sheet identity used by the simple data-first adapter. Defaults to sheet1.API reference →CellAddress.row: numberinterfaceCellAddress {row:number;}interfaceCellAddress {row:number;}0-based row of the cell that holds the top-left corner.API reference →CellAddress.col: numberinterfaceCellAddress {col:number;}interfaceCellAddress {col:number;}0-based column of the cell that holds the top-left corner.API reference →value: CellValuelet value:CellValuelet value:CellValueAPI reference →src: stringlet src:stringlet src:stringfunction createGrid(host: HTMLElement, opts: GridOptions): GridfunctioncreateGrid(host:HTMLElement,opts:GridOptions,):GridfunctioncreateGrid(host:HTMLElement,opts:GridOptions,):GridCreates and mounts an imperative Grid in the supplied host element.Exampleimport { createGrid, initSheetwrite } from"@sheetwrite/core";awaitinitSheetwrite();consthost= document.createElement("div");host.style.height ="400px";document.body.append(host);constgrid=createGrid(host, {workbook: {activeSheet: "sales",sheets: [{ id: "sales", name: "Sales", rowCount: 2,columns: [{ key: "amount", header: "Amount", type: "number", width: 120 }] }],},data: { rowCount: 2, columns: { amount: [12, 18] } },});grid.scrollToCell({ sheet: "sales", row: 1, col: 0 });// Call when the host view is removed.grid.destroy();See alsoGrid *See alsoGridOptionsAPI reference →function initSheetwrite(source?: BufferSource | URL | string | Request | WebAssembly.Module, engine?: SheetwriteEngine): Promise<void>functioninitSheetwrite(source?:BufferSource|URL|string|Request|WebAssembly.Module,engine?:SheetwriteEngine,):Promise<void>functioninitSheetwrite(source?:|BufferSource|URL|string|Request|WebAssembly.Module,engine?:SheetwriteEngine,):Promise<void>Load one WASM engine before createGrid. The default is @sheetwrite/wasm. Pass the @sheetwrite/formulas module as the second argument to select the full engine.Exampleimport { initSheetwrite } from"@sheetwrite/core";import*as formulas from"@sheetwrite/formulas";awaitinitSheetwrite(undefined, formulas);See also/docs/start/choose-an-engine/See alsocreateGridAPI reference →const host: HTMLElement | nullconsthost:HTMLElement|nullconsthost:HTMLElement|nullvar document: Documentvar document:Documentvar document:Documentwindow.document returns a reference to the document contained in the window. MDN ReferenceAPI reference →ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement | null (+4 overloads)ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement |null (+4 overloads)ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement |null (+4 overloads)Returns the first element that is a descendant of node that matches selectors. MDN Referenceinterface HTMLElementinterfaceHTMLElementinterfaceHTMLElementThe HTMLElement interface represents any HTML element. MDN Referencevar Error: ErrorConstructor
new (message?: string, options?: ErrorOptions) => Error (+2 overloads)var Error:ErrorConstructornew (message?:string, options?:ErrorOptions) =>Error (+2 overloads)var Error:ErrorConstructornew (message?:string, options?:ErrorOptions) =>Error (+2 overloads)const host: HTMLElementconsthost:HTMLElementconsthost:HTMLElementElementCSSInlineStyle.style: CSSStyleDeclarationinterfaceElementCSSInlineStyle {style:CSSStyleDeclaration;}interfaceElementCSSInlineStyle {style:CSSStyleDeclaration;}MDN ReferenceAPI reference →CSSStyleDeclaration.height: stringinterfaceCSSStyleDeclaration {height:string;}interfaceCSSStyleDeclaration {height:string;}MDN ReferenceAPI reference →GridOptions.workbook: WorkbookinterfaceGridOptions {workbook:Workbook;}interfaceGridOptions {workbook:Workbook;}Live workbook schema adopted by the store and updated by document operations.API reference →Workbook.activeSheet: stringinterfaceWorkbook {activeSheet:string;}interfaceWorkbook {activeSheet:string;}Active sheet ID and initial tab presented when the grid is created.API reference →Workbook.sheets: Sheet[]interfaceWorkbook {sheets:Sheet[];}interfaceWorkbook {sheets:Sheet[];}Sheets in display/tab order.API reference →Sheet.id: stringinterfaceSheet {id:string;}interfaceSheet {id:string;}Stable identifier, unique within the workbook and used by every cell address.API reference →Sheet.name: stringinterfaceSheet {name:string;}interfaceSheet {name:string;}User-facing sheet name shown in tabs and workbook exports.API reference →Sheet.rowCount: numberinterfaceSheet {rowCount:number;}interfaceSheet {rowCount:number;}Row count for both in-memory and datasource-backed sheets.API reference →Sheet.columns: Column[]interfaceSheet {columns:Column[];}interfaceSheet {columns:Column[];}Ordered schema; array positions are the zero-based column coordinates.API reference →Column.key: stringinterfaceColumn {key:string;}interfaceColumn {key:string;}Non-empty key, unique within the sheet, used to map input and datasource values.API reference →Column.header: stringinterfaceColumn {header:string;}interfaceColumn {header:string;}Schema label used by table exports and data-grid presentation headers.API reference →Column.type: CellFormatinterfaceColumn {type:CellFormat;}interfaceColumn {type:CellFormat;}Controls cell input parsing and default value formatting for this column.API reference →Column.width: numberinterfaceColumn {width:number;}interfaceColumn {width:number;}Unzoomed column width in CSS pixels.API reference →GridOptions.data?: ColumnarData | undefinedinterfaceGridOptions {data?:ColumnarData|undefined;}interfaceGridOptions {data?:ColumnarData|undefined;}Eager column-major values loaded into workbook.activeSheet; use instead of datasource.API reference →ColumnarData.rowCount: numberinterfaceColumnarData {rowCount:number;}interfaceColumnarData {rowCount:number;}Row count for both in-memory and datasource-backed sheets.API reference →ColumnarData.columns: Record<string, ArrayLike<CellScalar | CellValue>>interfaceColumnarData {columns:Record<string, ArrayLike<CellScalar|CellValue>>;}interfaceColumnarData {columns:Record<string,ArrayLike<CellScalar|CellValue>>;}Exact column runs represented by every returned row.API reference →a: number[]let a:number[]let a:number[]b: number[]let b:number[]let b:number[]result: null[]let result:null[]let result:null[]Search state after the replacement (matches re-scanned against the new data).API reference →