Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / June 2008

Tip: Looking for answers? Try searching our database.

condition for line color

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
cmar - 29 Jun 2008 21:59 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.