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.