colors sumif

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Colors sumif

Colors sumif

resolvedResolved · Low Priority · Version 2003

Joanna has attended:
Excel Advanced course

Colors sumif

hi, does anyone know how to sum up just cells in a particular colour? thanks

Edited on Fri 18 Jul 2008, 16:43

RE: colors sumif

Hi Joanna,

Thank you for your post, welcome to the forum;

I think I have got it, but you will need to use VBA;

Press Alt F11 to get into the VBA editor, then insert a module into your workbook. Into this paste the following:

Function SUMIFCOLOUR(TheRange As Range, TheColourCell As Range) As Variant

Dim TempRange As Range
Dim Result
Dim Colour

Application.Volatile
On Error Goto BailOut
Colour = TheColourCell.Interior.Color

For Each TempRange In TheRange
If Colour = TempRange.Interior.Color Then Result = Result + TempRange.Value
Next

BailOut:
SUMIFCOLOUR = Result

End Function


and hopefully you will get the result you need.

Hope this helps


 

Excel tip:

Line breaks in a cell

You can control the line breaks for multiple-line headings or labels in your Microsoft Excel worksheet, just like you do in Microsoft Word. Here's how to do it.

Click the cell where you want the label or heading to appear.
Type the first line of information.
Press ALT+ENTER.

View all Excel hints and tips


Server loaded in 0.11 secs.