Excel - SUMIF using color

Is there a way to only add cells in a column that have a certain colored
font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I
couldn't figure out if this can be done or not.

What I need specifically is a way to say: add G100 plus any numbers that are
blue from G1:G99.

Thanks in advance,
Kris

I think I may have just found the answer to my question at
http://www.xldynamic.com/source/xld.ColourCounter.html

Thanks

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
---
HTH

Bob

(there is no email, no snail mail, but somewhere should be gmail in my addy)

Yes it is possible, either use the info on Chip's site or this.
Regardless it involves VBA

http://www.xldynamic.com/source/xld.ColourCounter.html




--
Regards,

Peo Sjoblom

Hi Bob,

I went to the site and copied the code at the bottom. I went into
tools>macros>VBA then into Insert>Moduleand pased the code. Then I used the
following formula:
=SUMPRODUCT(--(ColorIndex(f3:f74,true)=6),f3:f74)
When I hit enter nothing happens. The formula is still just written in the
cell. When I evaluate the formula it just says "the cell currently being
evaluated contains a constant".
I have no idea what I'm doing wrong as this is way beyond my excel skills.
Do you have any thoughts?

Assuming that you have copied the ColorIndex function in correctly, the
formula should work. I have just reconstructed it and it works fine. You
appreciate that your formula is summing the yellow NUMBERS in F3:F74?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thanks so much for all of your help. I don't know what I was doing wrong but
it was driving me crazy so I gave up and went with the helper column to add
the colored cells.

Thanks again. Kris

As I said to you, because you set the text argument to TRUE in the
ColorIndex function call, you were not testing coloured cells, but coloured
text within those cells!!!

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Oh, Now I get it, thanks!

I need a totally idiot proof guide to doing this.  I tried pasting
'=SUMPRODUCT(--(ColorIndex(A1:A100)=3))' into a cell and chanign the 3 to 6
which i beleive value for yellow.  All I get is #NAME?.  Does this work in
Excel 2007?  Do i need to enable something?  Do i need to paste this code
into VBL?

Any help would be GREATLY appreciated!

Yep.  You need to copy that code into the VBE for that workbook.

Since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




--

Dave Peterson

This is a Romanian version of SumIf by Colors.

http://valygreavu.wordpress.com/2010/01/08/stupid-work-procedures-beautiful-solutions/