Home

How do I add cells with the same background color?

%3CLINGO-SUB%20id%3D%22lingo-sub-786302%22%20slang%3D%22en-US%22%3EHow%20do%20I%20add%20cells%20with%20the%20same%20background%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786302%22%20slang%3D%22en-US%22%3E%3CPRE%3E%3CSPAN%3EHow%20do%20I%20add%20cells%20with%20the%20same%20background%20color%3F%3C%2FSPAN%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-786302%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786336%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20add%20cells%20with%20the%20same%20background%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786336%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386559%22%20target%3D%22_blank%22%3E%40jmbcb%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20use%20a%20filter%20to%20filter%20the%20cells%20with%20the%20same%20background%20color%20and%20subtotal%20the%20column%20to%20total%20the%20cells%20with%20the%20same%20background%20color.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3CBR%20%2F%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786491%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20add%20cells%20with%20the%20same%20background%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786491%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%20thank%20for%20your%20quick%20reply%3C%2FP%3E%3CP%3E%3CSPAN%3Ewhat%20I'm%20trying%20to%20add%20is%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20205px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125485i8216C362B73ABE20%2Fimage-dimensions%2F205x273%3Fv%3D1.0%22%20width%3D%22205%22%20height%3D%22273%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECan%20you%20please%20help%20me%3F%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786915%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20add%20cells%20with%20the%20same%20background%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386559%22%20target%3D%22_blank%22%3E%40jmbcb%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20one%20possible%20way%20to%20achieve%20your%20goal.%20First%2C%20start%20by%20creating%20this%20Sub%20in%20VBA%20(Press%20Alt%2BF11%2C%20right%20click%20%22ThisWorkbook%22%2C%20Insert%20%26gt%3B%20Module%2C%20then%20copy%20and%20paste%20the%20following)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23999999%22%3E%3CEM%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSub%20SameColor()%3C%2FFONT%3E%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CFONT%20color%3D%22%23999999%22%3E%3CEM%3E%3CFONT%20size%3D%222%22%3EFunction%20ColorSum(CellColor%20As%20Range)%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FFONT%3E%3CFONT%20color%3D%22%23999999%22%3E%3CEM%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20ColorSum%20%3D%20CellColor.Interior.ColorIndex%3C%2FFONT%3E%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23999999%22%3E%3CEM%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BEnd%20Function%3C%2FFONT%3E%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20create%20a%20new%20column%20in%20column%20C%20named%20ColorSum.%20In%20cell%20C2%20put%20the%20formula%20%3DColorSum(A2)%20and%20copy%20this%20formula%20down%20to%20C6.%20You%20should%20get%20values%20of%2023%2C%2040%2C%2040%2C%2050%2C%2050%20in%20cells%20C2%3AC6%2C%20respectively.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%2C%20in%20cells%20B8%3AB10%20put%20the%20following%20formulas%3A%3C%2FP%3E%3CP%3ECell%20B8%3A%26nbsp%3B%3DSUMIF(%24C%242%3A%24C%246%2CColorSum(%24A%245)%2C%24B%242%3A%24B%246)%3C%2FP%3E%3CP%3ECell%20B9%3A%26nbsp%3B%3DSUMIF(%24C%242%3A%24C%246%2CColorSum(%24A%243)%2C%24B%242%3A%24B%246)%3C%2FP%3E%3CP%3ECell%20B10%3A%26nbsp%3B%3DSUMIF(%24C%242%3A%24C%246%2CColorSum(%24A%242)%2C%24B%242%3A%24B%246)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20I%20must%20warn%20you%20that%20this%20solution%20is%20set%20up%20specifically%20to%20solve%20the%20problem%20the%20way%20you%20have%20it%20designed.%20If%20you%20change%20the%20colors%20of%20cells%20A2%3AA6%2C%20or%20add%20more%20cells%20to%20the%20list%2C%20this%20solution%20may%20not%20work%20for%20you%20(i.e.%20it%20is%20not%20dynamic).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3CBR%20%2F%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E
jmbcb
New Contributor
How do I add cells with the same background color?
3 Replies

Hello @jmbcb,

 

You may use a filter to filter the cells with the same background color and subtotal the column to total the cells with the same background color.

 

Hope this helps!
PReagan

@PReagan 

@PReagan  thank for your quick reply

what I'm trying to add is

clipboard_image_0.png

 

Can you please help me??

 

 

 

@jmbcb,

 

Here is one possible way to achieve your goal. First, start by creating this Sub in VBA (Press Alt+F11, right click "ThisWorkbook", Insert > Module, then copy and paste the following):

 

     Sub SameColor()

          Function ColorSum(CellColor As Range)
          ColorSum = CellColor.Interior.ColorIndex

     End Function

 

Next, create a new column in column C named ColorSum. In cell C2 put the formula =ColorSum(A2) and copy this formula down to C6. You should get values of 23, 40, 40, 50, 50 in cells C2:C6, respectively.

 

Finally, in cells B8:B10 put the following formulas:

Cell B8: =SUMIF($C$2:$C$6,ColorSum($A$5),$B$2:$B$6)

Cell B9: =SUMIF($C$2:$C$6,ColorSum($A$3),$B$2:$B$6)

Cell B10: =SUMIF($C$2:$C$6,ColorSum($A$2),$B$2:$B$6)

 

Now, I must warn you that this solution is set up specifically to solve the problem the way you have it designed. If you change the colors of cells A2:A6, or add more cells to the list, this solution may not work for you (i.e. it is not dynamic).

 

Hope this helps!
PReagan

Related Conversations