SUMIF with color criteria

Hi sash,

Product-1 and Product-5 both have the same color (index 37)????

Hi,

May I suggest that you record product names in specific cells rather than trying to analyse data using colours. This is in any case fraught with problems since someone only has to choose a slightly different shade and you're in trouble.

You'd do better to record your data in a different layout. Have a five column database with columns for Date, Building Line, Shift, Product and Quantity. Building Line, Shift and Products could be drop down Validation cells to assist data capture.

Once you have this layout you'll find it far simpler to analyse your data with either a Pivot Table or SUMIFS() formulae.

Hi,
Attached is your solution. Hope this helps.

Hi sash,

I used index 7 for Prod - 5:

Sub ProdCol(): Dim wq As Worksheet, ws As Worksheet, wa As Workbook, r As Long, c As Long, i As Integer
Dim P As String, Q(9): Set wa = ActiveWorkbook: Set wq = wa.Worksheets("QTY"): Set ws = wa.Worksheets("Summary")
c = 5: Do Until wq.Cells(7, c + 1) = "": c = c + 1: Loop
For r = 8 To 42: For c = 5 To c
If wq.Cells(r, c) <> "" Then
i = wq.Cells(r, c).Interior.ColorIndex
Select Case i
Case 37: P = "Prod-1 ": Q(1) = Q(1) + wq.Cells(r, c)
Case 22: P = "Prod-2 ": Q(2) = Q(2) + wq.Cells(r, c)
Case 4: P = "Prod-3 ": Q(3) = Q(3) + wq.Cells(r, c)
Case 39: P = "Prod-4 ": Q(4) = Q(4) + wq.Cells(r, c)
Case 7: P = "Prod-5 ": Q(5) = Q(5) + wq.Cells(r, c)
Case 44: P = "Prod-6 ": Q(6) = Q(6) + wq.Cells(r, c)
Case 36: P = "Prod-7 ": Q(7) = Q(7) + wq.Cells(r, c)
Case 48: P = "Prod-8 ": Q(8) = Q(8) + wq.Cells(r, c)
Case Else: GoTo GetNext
End Select
ws.Cells(r - 4, c) = P & Chr(34) & wq.Cells(r, c) & Chr(34)
End If
GetNext: Next c: Next r
For i = 1 To 8
wq.Cells(3, 2 + 2 * i) = Q(i): Next i: Erase Q: End Sub

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

You're welcome!