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.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.