How To Count And Sum Cells Based On Background Color In Excel?

[hackertom]

How To Count And Sum Cells Based On Background Color In Excel?

[Pete_UK]

If the background colour has been applied through conditional formatting, then you could use a conditional sum formula (using SUMIF, SUMIFS or SUMPRODUCT etc.) which makes use of the criteria set up for the conditional formatting.

If the colour has been applied manually, or via some macro, then you would have to use a macro to sum the appropriate values.

Hope this helps.

Pete

[hackertom]

I try with this VBS :

Please Login or Register  to view this content.

this method work but the problem is with conditional formatting rules excel cell... e.g i have a rule when user add on cell number "6" then add black background color and sum 6 with next 6 cell this method not work with the script. if i add black color with paint tool then sun and count correct... can you help me there?
?

[davsth]

You should really stick to manual shading or conditional formatting, it gets messy using both. Conditional formatting is better, as it is quicker and can use inbuilt functions, rather than vba code, and stops the risk of 2 manual colours being a slight difference in colour or shade and not being counted. why not attack a sample workbook

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.