• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate Data Based On Cell Color

A

alamgiritbd

New Member
Hi,


Is it possible to calculate data based on cell color in excel?


e.g: column A has multiple value with cell formatting (color). it’s depends on conditional formatting (color). There are 3 colors. Red, green, blue. I need to calculate the total amount of red color value and also other 2 colors in B cell.


Thanks.
 
Alamgiritbd


You can use a User Defined Function (UDF)

Code: 
Copy the following code and paste it into a code module in the spreadsheet you are using

In Excel use it as =sum_color(F1:F20,F15)

EG: Sum F1:F20 where the color matches F15

[pre]Function sum_color(myRange As Range, myColor As Range) As Double
Dim summ As Double
summ = 0

For Each c In myRange
If c.Interior.Color = myColor.Interior.Color Then summ = summ + c.Text
Next

sum_color = summ

End Function
[/pre]
 
@Hui

Since OP is using CF to generate colors, I don't believe your UDF will work. (And it's a real pain to try and figure out which CF has been chosen for each cell in a range...and not always reliable)


@alamgirtitbd

Since your colors are being generated by a specific condition, it would be easier to simply do a sum based on those same conditions. What are the CF conditions that are causing the different colors?
 
Have a look at http://www.cpearson.com/excel/CFColors.htm

It should be able to help you
 
Hi Guys,


Thanks for the replays. Allow me clear again, pls check the link here you find what i want to do.

https://rapidshare.com/files/440943283/Data_calculate_based_on_cell_color.xlsx


Thanks again.
 
Hello Hui,


Thanks for the Summ function. Is it possible to have the same function for Count. I have tried the UDFs (from the references above) but somehow they dont work in my workbook (unlike the simple summ function provided by you) :)


@Chandoo: Thank you for this wonderful blog and the stuff u write in... It is always simple and neat !! :)


Regards,

Mano
 
@Mano

Great idea

I've changed the code slightly to allow it to do both Sum and Count


so use

To Count: =Sum_color(Range,Color Range,0)

To Sum : =Sum_color(Range,Color Range,1)

Default is Count: =Sum_color(Range,Color Range)

Code: 
Function sum_color(myRange As Range, myColor As Range, Optional myType = 0) As Double
Dim summ As Double
summ = 0
For Each c In myRange
If myType = 1 And c.Interior.Color = myColor.Interior.Color Then summ = summ + c.Text
If myType = 0 And c.Interior.Color = myColor.Interior.Color Then summ = summ + 1
Next
sum_color = summ
End Function
 
Last edited:
Dear Hui,


Thanks a bunch... Awesome !! and to think I spent half a day on this ;-)

Have a fantastic vacation :)


- Mano!
 
@Mano

Did you read the post at http://www.cpearson.com/excel/CFColors.htm

Referred to above
 
@Hui:


Yes I did go over the link,but it kind of went over my head :p ....

I used Luke's solution of using the condition to get the number. Thanks!
 
You must log in or register to reply here.
Back
Top