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 is useful when you want to combine
style detection with values from a different column:
=SUMPRODUCT(ISFONTSTYLE("A1:A100","bold")*B1:B100) —
counts bold cells in A where you weight by B. Also handy to
consolidate many separate COUNTSTYLE formulas on the same range into
one.
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:
=SUMPRODUCT(ISFONTSTYLE("A1:A100","italic")*B1:B100) —
style check on A, sum values from B. SUMSTYLE can only sum the
styled cells' own values.
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:
=SUMPRODUCT(ISFONTSTYLE("A1:A100","underline")*B1:B100)/SUMPRODUCT(ISFONTSTYLE("A1:A100","underline")*1)
— average B-values where A is underlined.
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 bold
Colors
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", "colorSource" [, include_empty])
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to check. Accepts array literal for multiple ranges. |
| colorSource | string |
A1 notation of a cell whose font color is used as the reference,
e.g. "C1"
|
| 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 is useful when style and values are
in different columns:
=SUMPRODUCT(ISFONTCOLOR("A1:A100","D1")*B1:B100) —
weight B-values by font color in A. Also useful to consolidate many
COUNTFONTCOLOR calls on the same range.
SUMFONTCOLOR
Sums values of cells whose font color matches a reference cell.
=SUMFONTCOLOR("range", "colorSource")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of the reference cell for font color. |
Example:
=SUMFONTCOLOR("B2:B100", "A2") — sums B-values
where font color matches A2
💡 ISFONTCOLOR lets you sum values from a different
column:
=SUMPRODUCT(ISFONTCOLOR("A1:A100","D1")*B1:B100) — font
color check on A, sum values from B. SUMFONTCOLOR can only sum the
matching cells' own values.
AVERAGEFONTCOLOR
Averages values of cells whose font color matches a reference cell.
=AVERAGEFONTCOLOR("range", "colorSource")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of the reference cell for font color. |
Example:
=AVERAGEFONTCOLOR("B2:B100", "A2")
💡 ISFONTCOLOR lets you average values from a
different column:
=SUMPRODUCT(ISFONTCOLOR("A1:A100","D1")*B1:B100)/SUMPRODUCT(ISFONTCOLOR("A1:A100","D1")*1)
— average B-values where A has the matching font color.
COUNTBACKGROUNDCOLOR
Counts cells whose background color matches a reference cell.
=COUNTBACKGROUNDCOLOR("range", "colorSource" [, include_empty])
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to check. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of a cell whose background color is used as the reference. |
| 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 is useful when color and values are in
different columns:
=SUMPRODUCT(ISBGCOLOR("A1:A100","C1")*B1:B100) — weight
B-values by background color in A. Also useful to consolidate many
COUNTBACKGROUNDCOLOR calls on the same range.
SUMBACKGROUNDCOLOR
Sums values of cells whose background color matches a reference
cell.
=SUMBACKGROUNDCOLOR("range", "colorSource")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of the reference cell for background color. |
Example:
=SUMBACKGROUNDCOLOR("B2:B100", "A2")
💡 ISBGCOLOR lets you sum values from a different
column:
=SUMPRODUCT(ISBGCOLOR("A1:A100","C1")*B1:B100) —
background color check on A, sum values from B. SUMBACKGROUNDCOLOR
can only sum the matching cells' own values.
AVERAGEBACKGROUNDCOLOR
Averages values of cells whose background color matches a reference
cell.
=AVERAGEBACKGROUNDCOLOR("range", "colorSource")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of the reference cell for background color. |
Example:
=AVERAGEBACKGROUNDCOLOR("B2:B100", "A2")
💡 ISBGCOLOR lets you average values from a
different column:
=SUMPRODUCT(ISBGCOLOR("A1:A100","C1")*B1:B100)/SUMPRODUCT(ISBGCOLOR("A1:A100","C1")*1)
— average B-values where A has the matching background color.
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", "colorSource")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to test. |
| colorSource | string | A1 notation of the reference cell for background color. |
Example:
=SUMPRODUCT(ISBGCOLOR("A2:A20","C1")*B2:B20)
ISFONTCOLOR
Returns TRUE or FALSE per cell based on whether its font color
matches a reference cell.
=ISFONTCOLOR("range", "colorSource")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to test. |
| colorSource | string | A1 notation of the reference cell for font color. |
Example:
=SUMPRODUCT(ISFONTCOLOR("A2:A20","C1")*B2:B20)
Color + 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", "colorSource", colorType, style [,
include_empty])
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of the reference cell for color comparison. |
| 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 is useful when color+style check
and values are in different columns:
=SUMPRODUCT(ISCOLORANDSTYLE("A1:A100","B1","bg","bold")*C1:C100)
— weight C-values by color+style match in A.
SUMCOLORANDSTYLE
Sums values of cells that match both a color and a font style
condition.
=SUMCOLORANDSTYLE("range", "colorSource", colorType, style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of the reference cell. |
| colorType | string |
"fc" = font color, "bg" = background
color
|
| style | string |
One of: "bold", "italic",
"underline", "line-through"
|
Example:
=SUMCOLORANDSTYLE("A1:A50", "B1", "fc", "italic")
💡 ISCOLORANDSTYLE lets you sum values from a
different column:
=SUMPRODUCT(ISCOLORANDSTYLE("A1:A100","B1","fc","italic")*C1:C100)
— color+style check on A, sum values from C.
AVERAGECOLORANDFONTSTYLE
Averages values of cells that match both a color and a font style
condition.
=AVERAGECOLORANDFONTSTYLE("range", "colorSource", colorType, style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of the reference cell. |
| colorType | string |
"fc" = font color, "bg" = background
color
|
| style | string |
One of: "bold", "italic",
"underline", "line-through"
|
Example:
=AVERAGECOLORANDFONTSTYLE("A1:A50", "B1", "bg", "bold")
💡 ISCOLORANDSTYLE lets you average values from a
different column:
=SUMPRODUCT(ISCOLORANDSTYLE("A1:A100","B1","bg","bold")*C1:C100)/SUMPRODUCT(ISCOLORANDSTYLE("A1:A100","B1","bg","bold")*1)
— average C-values where A matches color+style.
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", "colorSource", colorType, style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to test. |
| colorSource | 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 bold
Cross-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
sheet
COUNTALLSHEETS
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 sheets
AVERAGEALLSHEETS
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")
Filter
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", "colorSource")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to filter. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of a cell whose background color is used as the filter. |
Example:
=FILTERBGCOLOR("A1:A100", "C1") — lists all
non-empty values in A1:A100 with C1's background color
FILTERFONTCOLOR
Returns non-empty cells whose font color matches a reference cell,
as a spill array.
=FILTERFONTCOLOR("range", "colorSource")
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to filter. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of a cell whose font color is used as the filter. |
Example:
=FILTERFONTCOLOR("A1:A100", "C1")
FILTERFONTSTYLE
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 values
FILTERCOLORANDSTYLE
Returns non-empty cells that match both a color condition and a font
style, as a spill array.
=FILTERCOLORANDSTYLE("range", "colorSource", colorType, style)
| Parameter | Type | Description |
|---|---|---|
| range | string | A1 notation of the range to filter. Accepts array literal for multiple ranges. |
| colorSource | string | A1 notation of the reference cell for color comparison. |
| 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