For some or other reason Excel does not have a standard formula to count cells by colour. Herewith my simple count colour formulas embedded in an EXCEL add-in. Very handy if one needs to count cells that was classified by color to denote progress, status or other meanings. Subsequently decided to add some more functions in the pack – see below.
Once off install –
Download ZIP file and copy add-in to C:\Users\[YourUserName]\AppData\Roaming\Microsoft\AddIns. This is standard path to Excel add-ins or save to a file location of your choice. Invoke Excel choose Options then Add-Ins and select dpxFormulas.
Using the Formulas in the Pack:
Once installed, by entering =dpx in any cell will bring up the list of functions below.
=dpxCountColor(Range,Color) where Range is the selection area of cells to be counted and Color is the cell containing the specific color that must counted in the Range area. Use this formula to count the number of for example blue cells. Use Paintbrush to ensure you are counting the right colours.
=dpxGetColor(Color) where Color is the cell containing the specific Color you want to return a value from. Use this formula when you have colour coded cells and you want a value in another cell indicating the colour. Ideal for Pivot Tables where you want a transactional list with one of the columns(dimensions) the colours.
=dpxGetComments(Range) where Range is the cell containing the COMMENT you want to return a value from. Use this formula when you have comments in cells and you want to insert the comment into a cell. Opposite function to dpxSetComments.
=dpxSetComments(Range,Comment) where Range is the cell you want to insert the comment into and Comment is the cell containing the comment. Use this formula to take a value of a cell and insert it as a comment. Opposite function to dpxGetComments.