ExcelBanter - Adding figures of only one colour

Here's a couple of previous posts on the subject;)

Nimrod
MrExcel MVP

Joined: 30 Apr 2002
Posts: 2511
Location: Vancouver BC , Canada

Status: Offline

Reply with quote

ColorSumIf...Version 2

In this version the Function takes the color from the cell its in. The
only parameter you give to it is the number of the column you want
evaluated:

For example if you want to have your answer in D2 for the Column C Red
Cells then you enter the following Function in D2:
=ColorSumIf(3)
AND make the color of D2 RED.

Here is the code for this version:

code:
Public Function ColorSumIf(Cm)
Do
rwIndex = rwIndex + 1
With Cells(rwIndex, Cm)
If .Interior.ColorIndex =
ActiveCell.Interior.ColorIndex Then
ColorSumIf = ColorSumIf + .Value
End If
End With
Loop Until Len(Trim(Cells(rwIndex, Cm).Value)) = 0

End Function

Post Fri May 10, 2002 9:38 pm
View user's profile Send private message AIM Address

Lo Lun To
Board Regular

Joined: 10 May 2002
Posts: 8

Status: Offline

Reply with quote

I think a VBA solution would the best, but here's a non-VBA way.

- Define a name (lets say Clr) and type in the RefersTo box
=GET.CELL(38,INDIRECT("rc[-1]",FALSE))

- Insert a new column immediately after column A.

- In the new column put in cells B1:B5 =Clr

Cells B1:B5 will then show the ref numbers for the fill-colours used
for A1:A5

You can then use the numbers in B1:B5 for your SUMIF formula.

Column B could be kept hidden.

Note : The formulas in column B will only update on a sheet
recalculation.

--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=524278