"Sum only the colored cells" custom function

[dtencza]

http://www.cpearson.com/excel/CFColors.htm

I am trying to sum only the colored cells in a column range, and the colors
are applied with conditional formatting. Someone recommended the above page
to me, and it looks like exactly what I am looking for - I just can't get the
one function I'm after to work.

Onthis page, the IndexColorOfCF, ColorOfCF, and
CountOfCF all work for me. The SumByCFColorIndex does not return an error,
but it is always evaluating to zero. I am putting the range, then an integer
as the function arguments.

I tried 0,1,2,-1, and others to try and see if it would sum the values of
the colored cells in the range I specified. I tried to look at the code, but
I don't know what integer it is looking for for it to work.

It is the last function on this page. If this would work, I'm all set! If
someone who understands this code could tell me, that would be great.

Thanks for any help you can offer.

 

[Ken]

Chip's site has a lot of great stuff and I am not the one to explain
those functions; but, it seems to me that you could possibly avoid the
function you are trying to use by using the same conditional criteria
used to generate the color in the first place in a sumif formula to do
what you want.

Ken
Norfolk, Va

 

[Rowan]

The integer you use in this function needs to be the index of the colour you
are wanting to sum. Use ColorIndexOfCF on one of the coloured cells to work
out what the index is. Then use this as the integer in SumByCFColorIndex.

Of course the other question is if you are using condidtional formatting to
set the colours, surely you could use the same conditions in a sumif or
countif function?

Anyway , hope this helps
Rowan

 

[dtencza]

Thank you Rowan. That was absolutely awesome advice - it works!

I used the integer I got from ColorIndexOfCF in the SumbyCFColorIndex
function and it did exactly what I wanted it to do!!!

 

[dtencza]

Looks like I spoke too soon. The SumbyCFColorIndex is summing all the cells
in the range that have the conditional formatting rule set to change the cell
to the colorindex number I picked, regardless of whether the condition makes
the cell colored or not!

Is there anything else I can do to get it to sum only the colored cells, or
is that really how the function is supposed to work?

 

[Rowan]

I couldn't really say why this is not working for you. It works fine when I
test it in Excel 2002.

You might want to go the other route (also suggested by Ken) of using the
same conditions that create to conditional formatting in a sumif or
sumproduct function.

Regards
Rowan

 

[David McRitchie]

See the thread in misc where the question was also posted:
http://google.com/[email protected]

But as also mentioned you might be better off simply using the same
formulas that you use in the conditional formatting. Where I use them
if is simply for converting to HTML done only then not for something
that has to be constantly updated.

Hopefully Chip will fix his page, in the meantime try the corrected functions.

 

[dtencza]

=SUM(IF(I1:I10<J1:J10,I1:I10,0))

I used the above array formula to do it. My conditional formatting was
coloring the column 'I' cells if they were less than the column 'J' cells. I
read Chip's page about array formulas and it worked!

Thanks to all who tried helping me.

See the thread in misc where the question was also posted:
http://google.com/[email protected]

But as also mentioned you might be better off simply using the same
formulas that you use in the conditional formatting. Where I use them
if is simply for converting to HTML done only then not for something
that has to be constantly updated.

Hopefully Chip will fix his page, in the meantime try the corrected functions.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I couldn't really say why this is not working for you. It works fine when I
test it in Excel 2002.

You might want to go the other route (also suggested by Ken) of using the
same conditions that create to conditional formatting in a sumif or
sumproduct function.

Regards
Rowan