# sumif function and cell color

Posted by Mike on August 15, 2001 2:51 PM

I would like the sum of a set of numbers based on the color of the cell. I've input conditional formats to color the cell red if it meets certain criteria and now I want to just those cells. I have no VBA knowledge so the easier the better. Thanks

Posted by Mark W. on August 15, 2001 3:03 PM

Windows("Quote Package.xls").Activate ActiveWindow.WindowState = xlMaximized ActiveWindow.LargeScroll Down:=1 Range("E50:J50").Select Windows("quotelog.xls").Activate ActiveWindow.WindowState = xlMaximized Range("A5").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.Close ActiveWindow.LargeScroll Down:=-1 Range("C22:F22").Select End Sub

I now have a better understanding of your problem (sorry, I thought you wanted the macro to access the file that it resides in, "quotelog.xls" in this case). You could try this (which will assume you have only two files open, "quotelog.xls" and the other file, whatever you name it).

Sub Summary()

Dim FileName1 As String

Dim FileName2 As String

If Application.Windows.Count <> 2 Then

MsgBox prompt:="There can only be 2 files are open", _

Buttons:=vbCritical

Exit Sub

End If

Application.ScreenUpdating = False

Workbooks.Open FileName:="P:\Projects\Jones\Logs\quotelog.xls"

FileName1 = ActiveWorkbook.Name

Rows("5:5").Select

Selection.Copy

Selection.Insert Shift:=xlDown

ActiveWindow.ActivateNext

FileName2 = ActiveWorkbook.Name

Range("E50:J50").Select

Selection.Copy

Windows(FileName1).Activate

Range("A5").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Application.CutCopyMode = False

ActiveWorkbook.Save

ActiveWindow.Close

Range("C22:F22").Select

End Sub

Let me know if it works for you.

Regards,

Barrie

Posted by Mark W. on August 15, 2001 3:03 PM

Mike, if you assigned colors based on a condition(s)

then why don't you use that condition(s) in a SUMIF

construct? What's your condition(s)? I would like the sum of a set of numbers based on the color of the cell. I've input conditional formats to color the cell red if it meets certain criteria and now I want to just those cells. I have no VBA knowledge so the easier the better. Thanks

Posted by Mike on August 15, 2001 4:41 PM

My conditions are that if the cell is one of a list of numbers i.e. 1,2,3,or 4 then the cell be red based on the conditional format. How do input tat same condition into a sumif function. I thought sumif functions could only be based on one condition.

Mike, if you assigned colors based on a condition(s)

Posted by Mark W. on August 15, 2001 4:52 PM

You're right about the limitations of SUMIF(), but

keep in mind that this formula was added to Excel

so that oft-misunderstood array formulas could be

avoided (at least for simple cases). In your

case the array formula, {=SUM((A1:A5={1,2,3,4})+0)},

will suffice! If A1:A5 contains {1;3;6;7;4} this

formula will produce a count of 3. Remember,

array formulas must be enter using the Ctrl+Shift+Enter

key combination, and the outermost braces, {}, are

not entered by you -- they're supplied by Excel

in recognition of a properly entered array formula. My conditions are that if the cell is one of a list of numbers i.e. 1,2,3,or 4 then the cell be red based on the conditional format. How do input tat same condition into a sumif function. I thought sumif functions could only be based on one condition.

Posted by Mike on August 15, 2001 5:21 PM

I appreciate your help up to this point but maybe i didn't mke my self clear on one point. I need the sum of the highlighted cells instead of the Count. Your formula counts the number of cells that meet the criteria but I need to get the sum total. Sorry for the confusion.

You're right about the limitations of SUMIF(), but

Posted by Aladin Akyurek on August 15, 2001 11:41 PM

{=SUM((A1:A5={1,2,3,4})*(A1:A5)}

Posted by Mark W. on August 16, 2001 7:18 AM

> Sorry for the confusion.

The confusion was on my part. All you need to

do is make a slight modification to the array

formula...

{=SUM((A1:A5={1,2,3,4})*A1:A5)} I appreciate your help up to this point but maybe i didn't mke my self clear on one point. I need the sum of the highlighted cells instead of the Count. Your formula counts the number of cells that meet the criteria but I need to get the sum total. Sorry for the confusion.