Sum in Excel based on text (or cell) colour

  Stubacka 11:36 28 Jan 04
Locked

I have several large spreadsheets containing information about building contractors and numbers of items fitted into houses.
I would like to colour code many of these items so that they are specifically excluded (or included) in the sum totals at the ends of the rows and columns.
Is it possible to sum rows and columns based on the colour of the text or cells.
If so please explain in terms as simple as possible as I am not yet into manual coding etc. within Excel.

  Big Elf 12:08 28 Jan 04

You can use conditional formatting rules to set the colour of a cell from the Format menu. I'm still trying to work out the next bit but I thinks it's an array formula but I don't know much about them.

  Big Elf 12:11 28 Jan 04

Do the items you want to include or exclude follow a particular rule e.g. exclude all items over 1000?

  rogertjj 12:19 28 Jan 04

I think Stubacka wants to sum certain cells depending on whether or not they are coloured.

As far as I am aware, there is no built in function that lets you do it, but it could be done using VBA

  Tog 12:25 28 Jan 04

Take a step back, how are you colouring the text/cell in the first place?

  VoG II 12:53 28 Jan 04

The following macro will sum all cells in the selection that have a background colour of red.


Sub SumOfColor()

Dim sumIt As Single

Dim cell As Range

sumIt = 0

'check each cell in the selection that is also in the used range.

'This avoids having to check empty cells if an entire column

'or row is selected.

For Each cell In Intersect(Selection, ActiveSheet.UsedRange)

'the interior refers to the background of a cell

If cell.Interior.ColorIndex = 3 Then

sumIt = sumIt + cell.Value

End If

Next cell

MsgBox "the sum is " & sumIt

End Sub

  VoG II 12:55 28 Jan 04

Sub SumOfColor()

Dim sumIt As Single

Dim cell As Range

sumIt = 0

'check each cell in the selection that is also in the used range.

'This avoids having to check empty cells if an entire column or row is selected.

For Each cell In Intersect(Selection, ActiveSheet.UsedRange)

'the interior refers to the background of a cell

If cell.Interior.ColorIndex = 3 Then

sumIt = sumIt + cell.Value

End If

Next cell

MsgBox "the sum is " & sumIt

End Sub

  gazmania 13:12 28 Jan 04

Nice bit of VB there VoG. If that appears complex to a non power user, may I suggest using the "SUMIF" function? What ever criteria is being used to colour the font/cell, could be used as the criteria for sumif.

  VoG II 17:06 28 Jan 04

click here should help.

  Stubacka 17:21 02 Feb 04

Thanks people (particularly VoG in this instance).

I have been trying to do this for at least two years and you've solved it straight away. The "click here" from VoG was particularly helpful.

Thanks again - sorted.

This thread is now locked and can not be replied to.

Surface Pro 5 News - release date, UK price, features, specs

Animator Emanuele Kabu’s psychedelic video is a stunning tribute to Lisbon city

Best Mac antivirus 2017