Font Style
Count, sum, average or test cells based on their font formatting: bold, italic, underline or strikethrough.
COUNTSTYLE
Counts the number of cells in a range that match a given font style.
=COUNTSTYLE("range", style [, include_empty])
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to check, e.g. "A1:A100". Pass an array literal for multiple ranges: {"A1:A10","C1:C10"} |
| style | string | One of: "bold", "italic", "underline", "line-through" |
| include_empty optional | boolean | TRUE (default) counts empty cells too; FALSE counts only non-empty cells |
Example:
=COUNTSTYLE("B2:B50", "bold") — counts all bold cells in B2:B50
💡 ISFONTSTYLE lets you check style in one column and weight values from another.
Style check in column A, sum values from column B. Also useful for consolidating: one ISFONTSTYLE call covers the full range, replacing many separate COUNTSTYLE formulas.
=SUMPRODUCT(ISFONTSTYLE("A1:A100","bold")*B1:B100)SUMSTYLE
Sums the values of cells in a range that match a given font style.
=SUMSTYLE("range", style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to check. Accepts array literal for multiple ranges. |
| style | string | One of: "bold", "italic", "underline", "line-through" |
Example:
=SUMSTYLE("B2:B50", "italic") — sums values of italic cells
💡 ISFONTSTYLE lets you sum values from a different column than where the style lives.
Style check in column A, sum values from column B — unlike SUMSTYLE, which can only sum the styled cells' own values.
=SUMPRODUCT(ISFONTSTYLE("A1:A100","italic")*B1:B100)AVERAGESTYLE
Averages the values of cells in a range that match a given font style.
=AVERAGESTYLE("range", style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to check. Accepts array literal for multiple ranges. |
| style | string | One of: "bold", "italic", "underline", "line-through" |
Example:
=AVERAGESTYLE("B2:B50", "underline") — averages underlined cells
💡 ISFONTSTYLE lets you average values from a different column.
Style check in column A, average values from column B.
=SUMPRODUCT(ISFONTSTYLE("A1:A100","underline")*B1:B100)/SUMPRODUCT(ISFONTSTYLE("A1:A100","underline")*1)ISFONTSTYLE
Returns TRUE or FALSE for each cell in a range based on whether it matches a given font style. Useful with native formulas like SUMPRODUCT.
=ISFONTSTYLE("range", style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to check. |
| style | string | One of: "bold", "italic", "underline", "line-through" |
Example:
=SUMPRODUCT(ISFONTSTYLE("A2:A20","bold")*B2:B20) — sum B-values where A is boldColors
Count, sum, average or test cells based on font color or background color. Pass a cell address to read its color, or pass a hex color string directly (e.g.
"#46bdc6").COUNTFONTCOLOR
Counts cells whose font color matches the font color of a reference cell.
=COUNTFONTCOLOR("range", "colorFromCell" [, include_empty])
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to check. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of a cell whose font color is used as the reference, e.g. "C1" — or a hex color string directly, e.g. "#46bdc6" |
| include_empty optional | boolean | TRUE (default) includes empty cells; FALSE counts only non-empty cells |
Example:
=COUNTFONTCOLOR("A1:A50", "D1") — counts cells in A1:A50 with same font color as D1
💡 ISFONTCOLOR lets you check font color in one column and weight values from another.
Font color check in column A, sum values from column B. Also useful for consolidating: one ISFONTCOLOR call covers the full range, replacing many separate COUNTFONTCOLOR formulas.
=SUMPRODUCT(ISFONTCOLOR("A1:A100","D1")*B1:B100)SUMFONTCOLOR
Sums values of cells whose font color matches a reference cell.
=SUMFONTCOLOR("range", "colorFromCell")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of the reference cell for font color, e.g. "A2" — or a hex color string, e.g. "#46bdc6" |
Example:
=SUMFONTCOLOR("B2:B100", "A2") — sums B-values where font color matches A2
💡 ISFONTCOLOR lets you sum values from a different column than where the font color lives.
Font color check in column A, sum values from column B — unlike SUMFONTCOLOR, which can only sum the matching cells' own values.
=SUMPRODUCT(ISFONTCOLOR("A1:A100","D1")*B1:B100)AVERAGEFONTCOLOR
Averages values of cells whose font color matches a reference cell.
=AVERAGEFONTCOLOR("range", "colorFromCell")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of the reference cell for font color, e.g. "A2" — or a hex color string, e.g. "#46bdc6" |
Example:
=AVERAGEFONTCOLOR("B2:B100", "A2") — averages B-values where font color matches A2
💡 ISFONTCOLOR lets you average values from a different column.
Font color check in column A, average values from column B.
=SUMPRODUCT(ISFONTCOLOR("A1:A100","D1")*B1:B100)/SUMPRODUCT(ISFONTCOLOR("A1:A100","D1")*1)COUNTBACKGROUNDCOLOR
Counts cells whose background color matches a reference cell.
=COUNTBACKGROUNDCOLOR("range", "colorFromCell" [, include_empty])
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to check. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of a cell whose background color is used as the reference, e.g. "C1" — or a hex color string directly, e.g. "#46bdc6" |
| include_empty optional | boolean | TRUE (default) includes empty cells; FALSE counts only non-empty cells |
Example:
=COUNTBACKGROUNDCOLOR("A1:A50", "C1") — counts cells with same background as C1
💡 ISBGCOLOR lets you check background color in one column and weight values from another.
Background color check in column A, sum values from column B. Also useful for consolidating: one ISBGCOLOR call covers the full range, replacing many separate COUNTBACKGROUNDCOLOR formulas.
=SUMPRODUCT(ISBGCOLOR("A1:A100","C1")*B1:B100)SUMBACKGROUNDCOLOR
Sums values of cells whose background color matches a reference cell.
=SUMBACKGROUNDCOLOR("range", "colorFromCell")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of the reference cell for background color, e.g. "A2" — or a hex color string, e.g. "#46bdc6" |
Example:
=SUMBACKGROUNDCOLOR("B2:B100", "A2") — sums B-values where background color matches A2
💡 ISBGCOLOR lets you sum values from a different column than where the background color lives.
Background color check in column A, sum values from column B — unlike SUMBACKGROUNDCOLOR, which can only sum the matching cells' own values.
=SUMPRODUCT(ISBGCOLOR("A1:A100","C1")*B1:B100)AVERAGEBACKGROUNDCOLOR
Averages values of cells whose background color matches a reference cell.
=AVERAGEBACKGROUNDCOLOR("range", "colorFromCell")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of the reference cell for background color, e.g. "A2" — or a hex color string, e.g. "#46bdc6" |
Example:
=AVERAGEBACKGROUNDCOLOR("B2:B100", "A2") — averages B-values where background color matches A2
💡 ISBGCOLOR lets you average values from a different column.
Background color check in column A, average values from column B.
=SUMPRODUCT(ISBGCOLOR("A1:A100","C1")*B1:B100)/SUMPRODUCT(ISBGCOLOR("A1:A100","C1")*1)ISBGCOLOR
Returns TRUE or FALSE per cell based on whether its background color matches a reference cell. Compose with SUMPRODUCT for weighted totals.
=ISBGCOLOR("range", "colorFromCell")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to test. |
| colorFromCell | string | A1 notation of the reference cell for background color, e.g. "C1" — or a hex color string, e.g. "#46bdc6" |
Example:
=SUMPRODUCT(ISBGCOLOR("A2:A20","C1")*B2:B20) — sums B-values where A has C1's background colorISFONTCOLOR
Returns TRUE or FALSE per cell based on whether its font color matches a reference cell.
=ISFONTCOLOR("range", "colorFromCell")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to test. |
| colorFromCell | string | A1 notation of the reference cell for font color, e.g. "C1" — or a hex color string, e.g. "#46bdc6" |
Example:
=SUMPRODUCT(ISFONTCOLOR("A2:A20","C1")*B2:B20) — sums B-values where A has C1's font colorColor + Style
Combine a color filter and a font style filter in one formula — both conditions must match.
COUNTCOLORANDSTYLE
Counts cells that match both a color condition and a font style condition.
=COUNTCOLORANDSTYLE("range", "colorFromCell", colorType, style [, include_empty])
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of the reference cell for color comparison, e.g. "B1" — or a hex color string, e.g. "#46bdc6" |
| colorType | string | "fc" = font color, "bg" = background color |
| style | string | One of: "bold", "italic", "underline", "line-through" |
| include_empty optional | boolean | TRUE (default) counts empty cells; FALSE only non-empty |
Example:
=COUNTCOLORANDSTYLE("A1:A50", "B1", "bg", "bold") — count cells with B1's background color that are also bold
💡 ISCOLORANDSTYLE lets you apply the color+style check to a different column than where the values are.
Color+style check in column A, weight by values from column C.
=SUMPRODUCT(ISCOLORANDSTYLE("A1:A100","B1","bg","bold")*C1:C100)SUMCOLORANDSTYLE
Sums values of cells that match both a color and a font style condition.
=SUMCOLORANDSTYLE("range", "colorFromCell", colorType, style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of the reference cell, e.g. "B1" — or a hex color string, e.g. "#46bdc6" |
| colorType | string | "fc" = font color, "bg" = background color |
| style | string | One of: "bold", "italic", "underline", "line-through" |
Example:
=SUMCOLORANDSTYLE("A1:A50", "B1", "fc", "italic") — sums cells in A1:A50 with B1's font color that are also italic
💡 ISCOLORANDSTYLE lets you sum values from a different column than where the color+style lives.
Color+style check in column A, sum values from column C — unlike SUMCOLORANDSTYLE, which can only sum the matching cells' own values.
=SUMPRODUCT(ISCOLORANDSTYLE("A1:A100","B1","fc","italic")*C1:C100)AVERAGECOLORANDFONTSTYLE
Averages values of cells that match both a color and a font style condition.
=AVERAGECOLORANDFONTSTYLE("range", "colorFromCell", colorType, style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of the reference cell, e.g. "B1" — or a hex color string, e.g. "#46bdc6" |
| colorType | string | "fc" = font color, "bg" = background color |
| style | string | One of: "bold", "italic", "underline", "line-through" |
Example:
=AVERAGECOLORANDFONTSTYLE("A1:A50", "B1", "bg", "bold") — averages cells in A1:A50 with B1's background color that are also bold
💡 ISCOLORANDSTYLE lets you average values from a different column.
Color+style check in column A, average values from column C.
=SUMPRODUCT(ISCOLORANDSTYLE("A1:A100","B1","bg","bold")*C1:C100)/SUMPRODUCT(ISCOLORANDSTYLE("A1:A100","B1","bg","bold")*1)ISCOLORANDSTYLE
Returns TRUE or FALSE for each cell in a range based on whether it matches both a color condition and a font style condition. Compose with SUMPRODUCT to count or sum in a single formula execution — the recommended alternative to COUNTCOLORANDSTYLE and SUMCOLORANDSTYLE when many formulas are used in one sheet.
=ISCOLORANDSTYLE("range", "colorFromCell", colorType, style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to test. |
| colorFromCell | string | A1 notation of the reference cell, or a hex color string e.g. "#46bdc6" |
| colorType | string | "fc" = font color, "bg" = background color |
| style | string | One of: "bold", "italic", "underline", "line-through" |
Example:
=SUMPRODUCT(ISCOLORANDSTYLE("A2:A20","B1","bg","bold")*C2:C20) — sums C-values where A has B1's background color and is boldCross-sheet
Aggregate the same cell range across every sheet in the workbook with a single formula. Optionally exclude named sheets.
SUMALLSHEETS
Sums the same range across all sheets in the workbook.
=SUMALLSHEETS("range" [, excluded])
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to sum on each sheet, e.g. "A1:B10" |
| excluded optional | string or array | Sheet name(s) to exclude from the aggregation |
Example:
=SUMALLSHEETS("B2:B50") — sums B2:B50 on every sheetCOUNTALLSHEETS
Counts matching values across the same range on all sheets.
=COUNTALLSHEETS("range" [, countItem] [, excluded])
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to count on each sheet. |
| countItem optional | any | Value to match; if omitted, counts all non-empty cells. |
| excluded optional | string or array | Sheet name(s) to exclude. |
Example:
=COUNTALLSHEETS("A1:A50", "Done") — counts "Done" across all sheetsAVERAGEALLSHEETS
Averages the same range across all sheets in the workbook.
=AVERAGEALLSHEETS("range" [, excluded])
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to average on each sheet. |
| excluded optional | string or array | Sheet name(s) to exclude. |
Example:
=AVERAGEALLSHEETS("C2:C20") — averages C2:C20 on every sheetFilter
Extract matching cell values as a vertical spill array. Empty cells are excluded. Use the results as input for other formulas or display them in a separate area of your sheet.
FILTERBGCOLOR
Returns non-empty cells whose background color matches a reference cell, as a spill array.
=FILTERBGCOLOR("range", "colorFromCell")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to filter. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of a cell whose background color is used as the filter, e.g. "C1" — or a hex color string, e.g. "#46bdc6" |
Example:
=FILTERBGCOLOR("A1:A100", "C1") — lists all non-empty values in A1:A100 with C1's background colorFILTERFONTCOLOR
Returns non-empty cells whose font color matches a reference cell, as a spill array.
=FILTERFONTCOLOR("range", "colorFromCell")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to filter. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of a cell whose font color is used as the filter, e.g. "C1" — or a hex color string, e.g. "#46bdc6" |
Example:
=FILTERFONTCOLOR("A1:A100", "C1") — lists all non-empty values in A1:A100 with C1's font colorFILTERFONTSTYLE
Returns non-empty cells matching a font style, as a spill array.
=FILTERFONTSTYLE("range", style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to filter. Accepts array literal for multiple ranges. |
| style | string | One of: "bold", "italic", "underline", "line-through" |
Example:
=FILTERFONTSTYLE("A1:A100", "bold") — lists all bold non-empty valuesFILTERCOLORANDSTYLE
Returns non-empty cells that match both a color condition and a font style, as a spill array.
=FILTERCOLORANDSTYLE("range", "colorFromCell", colorType, style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to filter. Accepts array literal for multiple ranges. |
| colorFromCell | string | A1 notation of the reference cell for color comparison, e.g. "C1" — or a hex color string, e.g. "#46bdc6" |
| colorType | string | "fc" = font color, "bg" = background color |
| style | string | One of: "bold", "italic", "underline", "line-through" |
Example:
=FILTERCOLORANDSTYLE("A1:A100", "C1", "bg", "bold") — lists cells with C1's background that are also bold