"Sum only the colored cells" custom function

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.

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

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" wrote:

> 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.

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

"Rowan" wrote:

> 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" wrote:
>
> > 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.

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?

"dtencza" wrote:

> 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!!!
>
> "Rowan" wrote:
>
> > 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" wrote:
> >
> > > 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.

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

"dtencza" wrote:

> 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?
>
> "dtencza" wrote:
>
> > 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!!!
> >
> > "Rowan" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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.

See the thread in misc where the question was also posted:
http://google.com/groups?threadm=e7z...TNGP14.phx.gbl

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

"Rowan" <[email protected]> wrote in message news:[email protected]...
> 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
>
> "dtencza" wrote:
>
> > 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?
> >
> > "dtencza" wrote:
> >
> > > 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!!!
> > >
> > > "Rowan" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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.

=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.

"David McRitchie" wrote:

> See the thread in misc where the question was also posted:
> http://google.com/groups?threadm=e7z...TNGP14.phx.gbl
>
> 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
>
> "Rowan" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
> > "dtencza" wrote:
> >
> > > 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?
> > >
> > > "dtencza" wrote:
> > >
> > > > 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!!!
> > > >
> > > > "Rowan" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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.
>
>
>
>