How to SUM by Cell Colour in Excel without VBA

This guide will show you how to SUM by cell colour in Excel without VBA. Once you have learned to SUM based on coloured cells in Excel, you can also use COUNT, IF, IFS, SUMIF, SUMIFS, COUNIFS, SUBTOTAL, AGGREGATE, AVERAGE and other Excel functions to calculate based on the background fill colour of the cell.

Steps: How to SUM by Cell Colour in Excel without VBA?

Step 1: Identify the Cells to Sum

• First, identify the column or row you want to sum based on colour.
• Go near to the first cell of the amount in which there are coloured cells.
• In this example, click on the cell C2.

Step 2: Go to Name Manager

• Go to the Formulas tab on the Ribbon and click Define Name in the Defined Names group.
• Alternatively, go to Formulas > Name Manager > New.

Step 3: Update New Name

• Name: update the name of the Cell (for example, ColourCode)
• Scope: by default, it will be selected as Workbook, but you can select any specific cell from the drop-down list.
• Comment: you can update the comment or leave it as blank.
• Refers to: update the GET.CEL formula starting with 63 or 38 and type the cell number. In this example, update =GET.CELL(63,!B2) to find the colour code.

• Click OK and close the dialog box.

Step 4: Enter Colour Code Formula

• Go to cell near to the coloured column or row and enter colour code formula as defined in the name manager. In this example, we have given the cell name as Colour Code, so enter the formula in cell C2 as =ColourCode.
• Drag or copy and paste the formula to get the colour code for all cells. In this example, the C column returns colour code 44 for orange, 0 for no colour and 4 for green.

Step 5: SUM based on Colour

• Use the SUMIF or SUMIFS formula to calculate the total based on cell colours in Excel after you have identified the colour code.
• In this example, enter the colour code in cell E2 as 44 and use formula =SUMIFS(B:B,C:C,E2) in cell F2 to calculate the sum based on orange colour as 25,000.

• Similarly, you can use COUNT, COUNIF, COUNIFS, IF, IFS, SUBTOTAL, AGGREGATE, AVERAGE and other Excel functions to calculate based on the background fill colour code of the cell.

Step 6: Save Macro-enabled File Type

• If you have already saved the workbook before updating the cell name as colour code in Name Manager, then while saving the file after calculation you will get the error as:
  • The following features cannot be saved in macro-free workbooks.
  • Excel 4.0 function stored is defined names.
  • To save a file with these featured, click No, and then choose a macro-enable file type in the File Type list.
  • To continue saving as a macro-free workbook, click Yes.

• Click No and select the save as type as Excel Macro-Enabled Workbook (*.xlsm) as shown below.
• If you are using the Save As option, then select the location and update the file name also while saving.

Conclusion

You can sum coloured cells in Excel by using the GET.CELL and SUMIFS function. Without using VBA code, you can sum cells by colour in Excel. There is not any direct built in formula to sum by colour in Excel. You need to save the file as a macro-enabled workbook after using the GET.CELL function and getting the total of the coloured cells in Excel, otherwise you will get #NAME? error instead of the colour code.

Learn More