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:

Please Login or Register  to view this content.

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!