Suppose agar aap k pass aik excel ki file ha Jis m 1000 memebers ki detail ha aur aap ne un ki amount ya salary etc k cell ko color deya huwa ha Aur aap chchte ho k sirf un cell ko sum kiya jae jis k color yellow ya green etc ho.
It is possible in Microsoft Excel. Leekan is k leye aap ko VBA (Macro) istimal karna ho gi. Jis k leye follow these steps
1. Press Alt+F11
2. Insert menu m ja kar module select karain aur open hone wale window m following code enter karain.
-------------------------------------------------------------------------
Function Sumbycolor(cellcolor As Range, sumrange As Range)
Dim mycell As Range
Dim icol As Integer
Dim mytotal
icol = cellcolor.Interior.ColorIndex 'get the target color
For Each mycell In sumrange 'look at each cell in the designated range
'if the cell color matches the target color
If mycell.Interior.ColorIndex = icol Then
'add the value in the cell to the total
mytotal = WorksheetFunction.Sum(mycell) + mytotal
End If
Next mycell
Sumbycolor = mytotal
End Function
-----------------------------------------------------------
Now you can use this function like this.
=sumbycolor(cellcolor,sumrange)
where cellcolor -------The address of a cell with taget color( yani jis cell k color ko base bana chahte hain.
sumrange------The range of cell to be summed. ( yani jis range ko aap jama karna chahte hain.
=sumbycolor(a1,a1:a10)
see the attached picture.
Note Jis cell ko conditional format keya ho wo cell pa ye apply nahe hoga. Cell k background color he sum honge. Font agar color ho ga tu sum nahee honga.
may be this will help
regards
Bookmarks