Adding figures of only one colour

Hi,

I have a single column of figures some red, some green.

Is it possible to total only and green figures inputted into the column?

Thanks.

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.

See the article at this link

http://www.xldynamic.com/source/xld.ColourCounter.html


"Barney" wrote:

> Hi,
>
> I have a single column of figures some red, some green.
>
> Is it possible to total only and green figures inputted into the column?
>
> Thanks.
>
>
>