software

A B
1 x 3
2 y 2
3 z 4

If I fill color (Red) B1 and B3 then I should get the sum of B1 and B3.
How this function can be made in excel with the property of cell?

You need VBA

See www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;virajquot; gt; wrote in message
news
gt; A B
gt; 1 x 3
gt; 2 y 2
gt; 3 z 4
gt;
gt; If I fill color (Red) B1 and B3 then I should get the sum of B1 and B3.
gt; How this function can be made in excel with the property of cell?
I tried the function =SUMPRODUCT(--(ColorIndex(A1:A100)=3),A1:A100) to sum
colored cell but when i enter this funtion it gives value as #NAME? kindly
help to solve

Regards
quot;Bob Phillipsquot; wrote:

gt; You need VBA
gt;
gt; See www.xldynamic.com/source/xld.ColourCounter.html for a working
gt; solution
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;virajquot; gt; wrote in message
gt; news
gt; gt; A B
gt; gt; 1 x 3
gt; gt; 2 y 2
gt; gt; 3 z 4
gt; gt;
gt; gt; If I fill color (Red) B1 and B3 then I should get the sum of B1 and B3.
gt; gt; How this function can be made in excel with the property of cell?
gt;
gt;
gt;

THANK YOU IT WORKS

I copied the code and pasted in VBA module and the the function works. But
the value is not updateing automaticlly as excel does. every time i have to
double click the function to update the value. How to make the value change
when i change the color. Please Ref. below

A
1 100 (Red)
2 200
3 300 (Red)

Sum (400)

A
1 100 (Red)
2 200 (Red)
3 300

Sum (400) - this is not updateing automatically i have to double click then
this value is changing

RegardsYou will not get it to update automatically on change of a cell colour,
because that action does not trigger a worksheet recalculation, so the UDF
cannot automatically be fired. You could add

Application.Volatile

at the start of the function, and then use Alt-F9 to force a recalc when you
change a colour.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;virajquot; gt; wrote in message
...
gt; THANK YOU IT WORKS
gt;
gt; I copied the code and pasted in VBA module and the the function works. But
gt; the value is not updateing automaticlly as excel does. every time i have
to
gt; double click the function to update the value. How to make the value
change
gt; when i change the color. Please Ref. below
gt;
gt; A
gt; 1 100 (Red)
gt; 2 200
gt; 3 300 (Red)
gt;
gt; Sum (400)
gt;
gt; A
gt; 1 100 (Red)
gt; 2 200 (Red)
gt; 3 300
gt;
gt; Sum (400) - this is not updateing automatically i have to double click
then
gt; this value is changing
gt;
gt; Regards
gt;
gt;
gt;
gt;