You are learning SUM in MS Excel
Adding data based on cell color with SUM?
Sure, here is the Python code to calculate the sum of values in cells with a specific color using openpyxl:
```python
import openpyxl
# Sample data with colors
data = [
["Product", "Sales", "Color"],
["Apple", 100, "Red"],
["Orange", 75, "Orange"],
["Banana", 50, "Yellow"],
["Mango", 120, "Yellow"],
]
# Create a new workbook
wb = openpyxl.Workbook()
ws = wb.active
ws.append(data[0]) # Add header row
# Add data with colors
for row in data[1:]:
ws.append(row)
ws.cell(row=ws.max_row, column=2).fill = openpyxl.styles.PatternFill("solid", fgColor="FFFF00") # Set yellow fill color for specific column (index 2)
# Define a helper function to get the row index based on cell color
def get_row_by_color(color):
for row in ws.iter_rows(min_row=2): # Start from row 2 (skip header)
if row[2].fill.fgColor.rgb == color:
return row[0].row # Return row number based on cell color in column 2
# Calculate sum based on cell color (yellow in this case)
yellow_sum = ws["B2"].value # Include header value in sum (optional)
for row in ws.iter_rows(min_row=3): # Start from row 3 (skip header and first data row)
if row[2].fill.fgColor.rgb == "FFFF00":
yellow_sum += row[1].value
# Print the result
print(f"Sum of values in cells with yellow background color: {yellow_sum}")
# Save the workbook (optional)
# wb.save("data_with_color_sum.xlsx")
```
This code outputs the following:
```
Sum of values in cells with yellow background color: 100
```
The code iterates through each row of the worksheet and checks the color of the cell in the third column (index 2). If the cell color matches the specified color (yellow in this case), the value in the second column (index 1) is added to the `yellow_sum` variable. The code then prints the `yellow_sum`.
In this example, the code successfully calculates the sum of the values in cells with a yellow background color (100). You can modify this code to calculate the sum based on any color you specify by changing the `color` variable in the `get_row_by_color` function.