AblebitsCountAByColor function

The AblebitsCountAByColor function counts the number of cells with the specified background color. Can include empty cells or not depending on the argument.

For example:

  • =AblebitsCountAByColor(C3:I9,N22) Counts the number of non-blank cells with the same background color as the N22 cell in the range C3:I9.
  • =AblebitsCountAByColor(C3:I9,N22,FALSE) Counts the number of all cells (both empty and not empty) that have the same background color as the N22 cell in the range C3:I9.
  • =AblebitsCountAByColor(C3:I9,255) Counts the number of non-blank cells with the background color of 255 color code in the range C3:I9.
  • =AblebitsCountAByColor(C3:I9,255,FALSE) Counts the number of all cells (both empty and not empty) that have the background color of 255 color code in the range C3:I9.
Note. Formulas created with this function will work only on machines with installed Ultimate Suite for Excel. If the workbook is opened on another computer where Ultimate Suite is not installed, the result will be lost.
Tip. You can find the complete list of custom functions on the Ablebits functions for Excel page.

Syntax

AblebitsCountAByColor(range,color,skip_empty_cells)

The AblebitsCountAByColor function syntax has the following arguments:

  • range
    Required. The range of cells you want to count, e.g. B2:B8.
  • color
    Required. The background color of cells that should be counted. Can be a reference to one cell like B6 or color code like 255.
  • skip_empty_cells
    Optional: TRUE or FALSE. If you do not add this argument, the result will equal to TRUE and will not include empty cells into the count. However, if you add the FALSE argument, empty cells will be counted.

Remarks

  • The AblebitsCountAByColor function is used by the Count and Sum by Color add-in when you check the Insert result as formula box before pasting the result.
  • If you want to get the color code of the background color of a cell, you can use the AblebitsGetCellColor function.
  • Multiple ranges are not supported.
  • If you do not need to count logical values, text, or error values (in other words, if you want to count only colored cells that contain numbers), use the AblebitsCountByColor function.
  • The function does not work for conditionally formatted cells and for ranges formatted as tables with colored alternate rows or columns.

Please contact us here

Our working hours:
Pacific Time (PT) 11:00 PM (previous day) – 2:00 PM
Central European Time (CET) 08:00 - 23:00
Eastern Time (ET) 2:00 AM – 5:00 PM
Central Time (CT) 1:00 AM – 4:00 PM
Pacific Time (PT) 11:00 PM (previous day) – 2:00 PM
Australian Eastern Daylight Time (AEDT) 6:00 PM – 9:00 AM (next day)
If you want to attach files (e.g. screenshots or log files), it will be possible right after you send this form and we automatically create a support ticket for you.