Sum Cells Based on Color

[Sherry N.]

Hello,
I need to sum cells on sheet1 from sheet2 that have no fill or white
background.

In a cell on Sheet1 I used:
=SUMPRODUCT(--(ColorIndex('Sheet2'!C:C)=2),'Sheet2'!C:C)

But it didn't work. Any help?

Thanks,
Sherry N.

 

[Gary''s Student]

see:

http://www.cpearson.com/Excel/colors.htm

 

[Sherry N.]

Thanks but I must be doing something wrong. I copied the code, pasted in VB
editor and called the sumbycolor function but it's not working. Any other
solutions?

 

[Sherry N.]

I think it's not working because I am using conditional formatting to shade
my cells. Could that be it? Or might it be because the data is coming from a
query?

 

[Dave Peterson]

It's the conditional formatting that's the problem.

If you want to try to return the color based on conditional formatting, you
could review the code from Chip Pearson's site:

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

I think it's far from trivial. You may want to use another cell that mimics the
same conditions, but returns a number. It may be lots easier.

 

[Sherry N.]

Na, won't work I am using And and Formula is. Thanks a lot though.