Sum up Cells Based on Background Color Using SUMIF

Below are steps on how to sum up cells based on background color using Function SUMIF and UDF (User Defined Function).

Expectation:
E2=B2+B5+B6; E3=B3+B8+B10; E4=B4+B6+B9

Step 1. Create User Defined Function (UDF)  COLORINDEX

• Press Alt-F11
• Select Insert > Module

• Type the codes

Step 2. On the worksheet, create another column and use the UDF COLORINDEX to get the Color Index number. In this case, cell C2.

Copy for the next cell C3 to C10 and then we can use Function SUMIF for cell F2, F3, and F4 as below:

• Sum up Yellow color. Cell F2:  =SUMIF(C2:C10,C2,B2:B10) 

• Sum up Red color. Cell F3:  =SUMIF(C2:C10,C3,B2:B10) 

• Sum up Purple color. Cell F4:  =SUMIF(C2:C10,C4,B2:B10)