Excel - SUMIF using color

Asked By Kle on 13-Jul-07 01:18 PM
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,

Kle replied on 13-Jul-07 01:24 PM
I think I may have just found the answer to my question at

Bob Phillips replied on 13-Jul-07 01:24 PM
See http://www.xldynamic.com/source/xld.ColourCounter.html for a working



(there is no email, no snail mail, but somewhere should be gmail in my addy)
Peo Sjoblom replied on 13-Jul-07 01:24 PM
Yes it is possible, either use the info on Chip's site or this.
Regardless it involves VBA



Peo Sjoblom
Kle replied on 13-Jul-07 01:54 PM
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:
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?
Bob Phillips replied on 13-Jul-07 05:43 PM
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?



(there's no email, no snail mail, but somewhere should be gmail in my addy)
Kle replied on 13-Jul-07 06:44 PM
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
Bob Phillips replied on 14-Jul-07 10:26 AM
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!!!



(there's no email, no snail mail, but somewhere should be gmail in my addy)
Kle replied on 14-Jul-07 02:50 PM
Oh, Now I get it, thanks!
mshaw151 replied on 07-Dec-07 02:15 PM
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!
Dave Peterson replied on 07-Dec-07 05:04 AM
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:


Dave Peterson
Valy GREAVU replied to mshaw151 on 13-Jan-10 08:09 AM
This is a Romanian version of SumIf by Colors.