Autosum Range of Cells by Background Color

%3CLINGO-SUB%20id%3D%22lingo-sub-2286806%22%20slang%3D%22en-US%22%3EAutosum%20Range%20of%20Cells%20by%20Background%20Color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286806%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20id%20like%20to%20say%20thank%20you%20to%20whomever%20is%20willing%20to%20help%20me%20with%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20spreadsheet%20to%20help%20me%20track%20my%20monthly%20expenses.%20For%20this%20example%20I%20will%20use%20Groceries.%20I%20track%20each%20purchase%20by%20color%20coding%20each%20store%20a%20different%20color.%20So%20the%20background%20of%20each%20purchase%20is%20manually%20highlighted%20depending%20on%20which%20store%20the%20purchase%20was%20made.%20I%20am%20trying%20to%20figure%20out%20how%20to%20autosum%20the%20amount%20spent%20each%20month%20by%20the%20background%20color%20of%20the%20cells.%20I%20have%20attached%20an%20example.%20I%20hope%20I%20have%20explained%20this%20clearly%20enough.%20Please%20let%20me%20know%20if%20you%20have%20any%20questions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2286806%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2286883%22%20slang%3D%22en-US%22%3ERe%3A%20Autosum%20Range%20of%20Cells%20by%20Background%20Color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286883%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1035847%22%20target%3D%22_blank%22%3E%40bdubs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20%3CA%20href%3D%22http%3A%2F%2Fwww.cpearson.com%2Fexcel%2Fcolors.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EColor%20Functions%20In%20Excel%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2286948%22%20slang%3D%22en-US%22%3ERe%3A%20Autosum%20Range%20of%20Cells%20by%20Background%20Color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2286948%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20reply.%20I%20followed%20the%20link%20and%20am%20still%20not%20100%25%20sure%20on%20how%20to%20do%20this.%20Using%20my%20spreadsheet%20example%20in%20my%20previous%20post%20I%20used%20the%20following%20formula%20in%20cell%20C20%20and%20the%20result%20is%20%22%23NAME%3F%22.%20%3CFONT%20color%3D%22%23FF0000%22%3E%3DSUM(C6%3AC19*(COLORINDEXOFRANGE(C6%3AC19%2CFALSE%2C1)%3D3))%3C%2FFONT%3E%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

First id like to say thank you to whomever is willing to help me with this.

 

I have created a spreadsheet to help me track my monthly expenses. For this example I will use Groceries. I track each purchase by color coding each store a different color. So the background of each purchase is manually highlighted depending on which store the purchase was made. I am trying to figure out how to autosum the amount spent each month by the background color of the cells. I have attached an example. I hope I have explained this clearly enough. Please let me know if you have any questions.

 

Thank you again.

4 Replies

Thank you for your reply. I followed the link and am still not 100% sure on how to do this. Using my spreadsheet example in my previous post I used the following formula in cell C20 and the result is "#NAME?". =SUM(C6:C19*(COLORINDEXOFRANGE(C6:C19,FALSE,1)=3)) @Hans Vogelaar 

@bdubs 

You need to create some code in the Visual Basic Editor and save the workbook as a macro-enabled workbook (.xlsm)

I have added a much simplified version of the SumColor function from that article to your workbook.

Make sure that you allow macros when you open the workbook.

 

S0343.png

Thank you for all of your help! @Hans Vogelaar