MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.