Use cell fill (interior color index) as criteria in sumif or count if

[Alex00001]

Hello,

Is it possible to create a criteria based formula in excel 2003 that uses cel formatting as a criteria? (without the use of VBA)
For example, i have a list of values which all have different cell fill colors.

1 (green cell fill)
3 (yellow cell fill)
5 (blue cell fill)
9 (green cell fill)
2 (yellow cell fill)

Is there a formula that can do the following:
sumif cell fill is green = 10
sumif cell fill is yellow= 5

Thanks a bunch for your help!

Alex

 

[Special-K99]

I believe this is only possible with VBA (unless the colour settings are dependant on Conditional Formatting)

 

[yytsunamiyy]

Short and sweet answer - it's not going to work without vba. Excel 2007 offers you the option to sort by color, but adding by color is not (yet?) supported. If you want to do that you will need to go down the vba route. See also:
http://www.cpearson.com/excel/SortByColor.aspx

 

[Alex00001]

This works like a charm!
Thanks for the quick and usefull reply yytsunamiyy!

 

[yytsunamiyy]

Hello,

Is it possible to create a criteria based formula in excel 2003 that uses cel formatting as a criteria? (without the use of VBA)

Glad the answer was helpful to you. Please note though that by following the steps on Chips site you actually added a UDF (User Defined Function) - that is a vba module - to your workbook, which means that your workbook now contains macros that must be enabled for the function to work.

 

[RoryA]

FYI, you can do it without VBA if you use the old XLM functions, but I don't recommend either approach to be honest.