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
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies