Sum Cells Based on Background Color

Can anyone help me to get the total amount based on the colour of cell? Thanks for advance.

Originally Posted by alferd324

. . . total amount based on the colour of cell . . .?

This sort of thing is unreliable. For example, if you have 1 to 10 in B3:B12, you want to sum cells with red background color, and B5, B7 and B10 have red background color, so the sum would be 16. Using volatile VBA user-defined functions, you could enter a formula like =MyColorSum(B3:B12,"red") and it'd return 16, but if you then change the background color of any of these 3 cells to something else or change the background color of any of the other 7 cells to red, the formula WILL NOT RECALCULATE until you press [F9] (assuming the UDF were volatile) or reenter the formula.

You'd be A LOT BETTER OFF using an extra column to enter, say, X for rows you want to include, then use conditional formatting based on formulas referring to these new entry cells to set the conditional formatting color for the value cells, then use SUMIFS(value_range,new_entry_range,"X") to sum up the selected values. Putting this differently, you're BEGGING FOR PROBLEMS basing any calculations on formatting.

Hi thanks for your suggestion. Can I know why this code can't work in my other file, as I already copy the code into my Visual Basic library of this file? And how to find the number of Color code?

Originally Posted by BMV

It's worse idea because if you only change color calculation value doesn't change till F9 or any other events for calculation will be done.
there are many variants and here oe of them

Please Login or Register  to view this content.

Hi thanks for your suggestion. Can I know why this code can't work in my other file, as I already copy the code into my Visual Basic library of this file? And how to find the number of Color code?

alferd324 .it's example but not code for distribute. For color code I prefer to use additional argument with cell was formatted.

Please Login or Register  to view this content.

Originally Posted by BMV

It's worse idea because if you only change color calculation value doesn't change till F9 or any other events for calculation will be done.
there are many variants and here one of them

Please Login or Register  to view this content.

Originally Posted by BMV

alferd324 .it's example but not code for distribute. For color code I prefer to use additional argument with cell was formatted.

Please Login or Register  to view this content.

Can you make it work on the Colour Fill by conditional formatting as it only works in fill colour manually?

The string must be changed but I cant check it now

Please Login or Register  to view this content.

But in the case you use conditional format then you can use sumif(s) with the same conditions.

Originally Posted by BMV

The string must be changed but I cant check it now

Please Login or Register  to view this content.

But in the case you use conditional format then you can use sumif(s) with the same conditions.

Sorry it can't work for me

Yes, i'v checked and DisplayFormat is not available for UDF from worksheet.

If the colour is from a conditional format, I refer you to post 3. it is possible to sum based on the conditional format if we know what it is. Which is why an attachment like suggested in the yellow bar is hugely helpful. your example doesn't contain the conditional formats

Originally Posted by davsth

If the colour is from a conditional format, I refer you to post 3. it is possible to sum based on the conditional format if we know what it is. Which is why an attachment like suggested in the yellow bar is hugely helpful. your example doesn't contain the conditional formats

Below is the attachment that I applied the conditional formatting into it. In this file, I enter the serial number in sheet 2 and then the same serial number will be highlighted in sheet 1

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by BMV

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks for your help