Formula or Macro help - using colours to get a total sum??

CarolSweet 

If you want to assign values to colors in Excel and then calculate the total cost based on those colors, you might consider using a combination of Conditional Formatting and helper columns with formulas or VBA.

Here is a step-by-step guide:

Method 1: Using Conditional Formatting and Helper Columns

1. Assign Values to Colors:
• Choose a range of cells where you will be assigning colors and values.
• Apply Conditional Formatting to those cells. Set up rules for each color and assign a unique number to each rule.
2. Helper Column:
• In an adjacent column, use a formula to look up the values based on the color using the CELL and GET.CELL functions.

=IFERROR(CHOOSE(GET.CELL(38, A1), 10, -- Value for Color1, 20, -- Value for Color2, 30 -- Value for Color3 ), 0)

• Adjust the cell references and values accordingly.
1. Calculate Total:
• Use a SUM formula to calculate the total based on the helper column.

=SUM(B:B)

Method 2: Using VBA (Visual Basic for Applications)

1. Insert Module:
• Press Alt + F11 to open the VBA editor.
• Insert a new module: Insert -> Module.
2. VBA Code:
• Use the following VBA code and modify it according to your color-value pairs.

Vba code:

Function GetColorValue(rng As Range) As Double
    Select Case rng.Interior.Color
        Case RGB(255, 0, 0): GetColorValue = 10   ' Red
        Case RGB(0, 255, 0): GetColorValue = 20   ' Green
        Case RGB(0, 0, 255): GetColorValue = 30   ' Blue
        Case Else: GetColorValue = 0
    End Select
End Function

Use in Worksheet:

• Back in Excel, use the following formula in a cell to get the value for a given colored cell:

=GetColorValue(A1)

• Modify the cell reference as needed.
1. Calculate Total:
• Use the SUM function to calculate the total based on the helper column.

Both methods allow you to assign values to colors and calculate totals based on those values. Choose the one that fits your requirements and comfort level with formulas or VBA. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

CarolSweet 

Certainly! If you want to use colors to calculate a total sum in Excel, you can achieve this with the help of a formula or a macro. Here's a simple way to do it using a formula:

Using Formulas:

Assuming you have a column of numbers with corresponding colors in another column:

1. Assign Numbers and Colors:

   • Assign numbers to cells in one column and colors (e.g., using conditional formatting) to cells in another column.

2. Use the SUM Function with CELL and GET.CELL:

   • Suppose your numbers are in column A and colors are in column B.
   • In an empty cell, use the following formula:
     excelCopy code

     =SUM(IF(GET.CELL(63,INDIRECT("RC[-1]",0))=3,INDIRECT("RC[-2]",0),0))
     This formula sums the numbers in column A where the corresponding cell in column B has a specific color. In this example, color code 3 represents a specific color.

3. Adjust for Your Color:

   • Replace the 3 in the formula with the color code corresponding to your desired color.

Using VBA Macro:

If you prefer a macro solution, you can use the following steps:

1. Open the Visual Basic for Applications (VBA) Editor:

   • Press Alt + F11 to open the VBA Editor in Excel.

2. Insert a New Module:

   • In the VBA Editor, right-click on any item in the Project Explorer, choose "Insert," and then select "Module."

3. Enter the Macro Code:

   • Copy and paste the following VBA code into the module:
     vbaCopy code

     Function SumByColor(rngSumRange As Range, cellColor As Range) As Double Dim sumValue As Double Dim cell As Range Application.Volatile sumValue = 0 For Each cell In rngSumRange If cell.Interior.Color = cellColor.Interior.Color Then sumValue = sumValue + cell.Value End If Next cell SumByColor = sumValue End Function

4. Use the Macro Function:

   • Back in your Excel workbook, you can use the new function like a regular Excel function. For example, if you want to sum numbers in column A with a specific color in column B:
     excelCopy code

     =SumByColor(A:A, B1)
     Replace B1 with a cell that has the color you want to use for summationhttps://baitmeetz.co.il/

Remember to save your workbook as a macro-enabled workbook if you're using VBA.

Choose the method that suits your preference – whether it's using a formula or a VBA macro – and adapt it to your specific Excel setup.