This is a Romanian version of SumIf by Colors.
http://valygreavu.wordpress.com/2010/01/08/stupid-work-procedures-beautiful-solutions/
mshaw151 wrote:
I need a totally idiot proof guide to doing this.
07-Dec-07
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!
:
Previous Posts In This Thread:
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
RE: SUMIF using color
I think I may have just found the answer to my question at
http://www.xldynamic.com/source/xld.ColourCounter.html
Thanks
:
Re: SUMIF using color
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.
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.
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
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.
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
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)
Re: SUMIF using color
Oh, Now I get it, thanks!
:
Yep. You need to copy that code into the VBE for that workbook.
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
mshaw1515 wrote:
--
Dave Peterson
I need a totally idiot proof guide to doing this.
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!
:
Submitted via EggHeadCafe - Software Developer Portal of Choice
WebClient Class: Gotchas and Basics
http://www.eggheadcafe.com/tutorial...305-3c644cac25a9/webclient-class-gotchas.aspx