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,
Kris




Kle replied on 13-Jul-07 01:24 PM
I think I may have just found the answer to my question at
http://www.xldynamic.com/source/xld.ColourCounter.html

Thanks
Bob Phillips replied on 13-Jul-07 01:24 PM
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)
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

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




--
Regards,

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:
=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?
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?

--
---
HTH

Bob

(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!!!

--
---
HTH

Bob

(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:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




--

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

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