# SUM & COUNT Cell Color using SUMPRODUCT Function

Welcome to Excel Jet Consult blog post. In this blog post, we learn how to use the elegant SUMPRODUCT Excel Function to SUM & COUNT based on cell color.

Sample Data

We have a sample dataset that spans from columns A to C. In Columns B and C respectively, we have Account Manager name and the corresponding Sales Amount color-coded with unique cell color. All the Sales Amount for Natasha Love, Yvonne Matthew, Connor Rulibeth and Abiola David were color-coded using Orange, Light Blue, Red and Purple cell colors respectively

Create Named Range

The next thing we want to do is to create a named range in the Name Manager using the Get.cell function.

• In the Defined Names group of the Formulas tab, click on Name Manager or deploy CTRL + F3
• Click on New
• In the New Name dialogue box, provide SumofColor for Name
• In the Refers to cell reference box, provide =GET.CELL(38,!C2)

Note, the Dataset has been formatted as an Excel Table using CTRL + T

In the Name Manager dialogue box caption below, we can see our two Tables and the SumofColor named range

Display Unique Cell Color Number

Next, in column D, we want to display the number for each of the specified cell color. Therefore,

• In cell D2, execute the formula: =SumofColor
• Hit Enter on the keyboard

From the caption below, we can see the unique number for each of the four cell colors

Color Orange is 44, Light Blue is 33, Red is 3 and Purple is 29

In cells G8 to G11, we have the four unique cell colors as seen in the caption below

SUM of Cell Color with SUMPRODUCT Function

In the next step, we want to sum all the Sales Amount based on cell color using the SUMPRODUCT Function which by definition, returns the sum of products of corresponding ranges or arrays

Therefore,

• In cell H8 of Sum sheet tab, execute the formula: =SUMPRODUCT((Sum_tbl[Sales Amount])*(Sum_tbl[Color Code]=SumofColor)). Do refer to the video for more in-depth explanation of the formula
• Click Enter and copy down the formula using fill handle

From the caption below, the sum of Sales Amount color-coded in Red which is for Connor Rulibeth is 838,492.

If you check the Status Bar after selecting all the cells coded in Red in column C, you will see that the result tallied which implies the calculation is accurate. The same is true for other Account Managers.

COUNT of Cell Color with SUMPRODUCT Function

Next, we want to count all the Sales Amount based on cell color using the SUMPRODUCT Function.

Therefore,

• In cell H8 of COUNT sheet tab, execute the formula: =SUMPRODUCT((Count_tbl[Sales Amount])*(Count_tbl[Color Code]=SumofColor)). Do refer to the video for more in-depth explanation of the formula
• Click Enter and copy down the formula using fill handle

From the caption below, the count of Sales Amount color-coded in Red which is for Connor Rulibeth is 5. This means that Connor Rulibeth had 5 Sales records

In the Status Bar, we have Count of 5.

Conclusion

We have seen how to use the powerful SUMPRODUCT Function that is able to perform AND logical operation. Do watch the video above and subscribe to my YouTube channel for more videos.

Posted in Blog and tagged .