I want to create an array formula but need help for a condition that refers
to the cell's color.
My formula would be like this:
{=SUM(IF(condition,VTC,0))}
where VTC is the named range =Consignt!$F:$F
I have colored many lines in the worksheet with a background color of
yellow, blue or pink. I want my "condition" to be that the cell has no
background color, e.g., is white.
How would I express that condition?
Thanks, CMA
Don Guillett - 29 Jun 2008 23:26 GMT
to count for yellow. For None use 0 instead of 6
=SUMPRODUCT(--(ColorIndex(A1:A21)=6))
to sum b based on a
=SUMPRODUCT(--(ColorIndex(A1:A21)=6),b1:b21)

Signature
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
>I want to create an array formula but need help for a condition that refers
>to the cell's color.
[quoted text clipped - 11 lines]
>
> Thanks, CMA
MartinW - 30 Jun 2008 02:19 GMT
Just adding to Don's post.
To get ColorIndex to work you will need to get the
code from Bob Phillips's site.
http://xldynamic.com/source/xld.ColourCounter.html
HTH
Martin
> to count for yellow. For None use 0 instead of 6
> =SUMPRODUCT(--(ColorIndex(A1:A21)=6))
[quoted text clipped - 15 lines]
>>
>> Thanks, CMA
Don Guillett - 30 Jun 2008 13:04 GMT
Oops.

Signature
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
> Just adding to Don's post.
>
[quoted text clipped - 24 lines]
>>>
>>> Thanks, CMA