< Go Back

Subtotal by color

The Excel SUMIF function is used to perform the addition of cells that meet a single criterion. This function can be used to perform addition of cells based on numbers, dates, and text that match certain criteria. You can easily perform subtotal by the color of numbers in Excel with the help of SUMIF function.  

Formula

=SUMIF(range_color,criteria,range_number)

Explanation

The formula incorporating Excel SUMIF function to perform subtotal by the color of numbers uses the following arguments:

  • range_color (required): This is that range of cells to which you want to apply the criteria against.
  • criteria (required): This is the criteria which determine which cells we need to add.
  • range_number (optional): These are the cells which are to be added together.  

This works as follows:

  • First, the criteria are applied to the range of cells which is specified by the range_color.
  • The reference obtains the criteria and then range_number specifies the cells that are to be added together.

Example

In this example, to perform subtotal by the color of the numbers we use the following formula;

=SUMIF(B5:B12,F6,D5:D12)

This works as follows:

  • Here the range_color is B5:B12 to which we apply the Red color criteria.
  • The specified range_number is D5:D12 which are the cells to be added.   

Figure 1. Example of SUMIF function to perform subtotal by the color of numbers in Excel 1

Notes

If you want to count or add cells which are filled with specific colors, it will be a more difficult task. If you plan to use conditional formatting to apply colors, you can probably use the same logic to count or add cells with existing formulas. If the cells are filled with colors manually, you’ll be required to use a different approach.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar