Formulas by Cell Color

[Bill F]

In Excel 2007 is it possible to sum cells by cell shading?

 

[AlphaFrog]

I don't think there's a specific function for that.

If you want to sum cells that are colored by Conditional Formatting, you can duplicate the Conditional Formatting "conditions" within a formula and sum cells that match those conditions.

If you have "manually" formatted certain cells (say every odd row), and you want to sum cells in a given range that have the format, often there are ways to replicate that format pattern e.g. every odd cell in a range.

If you give more specifics on what exactly you want to do and how your data is configured (examples are always good), usually someone around here will come up with a solution.

 

[Peter_SSs]

Bill

Welcome to the MrExcel board!

Probably best to do this via a macro but you may wish to try this work-around.

Given my data and colouring in column A below:

1. Select B1

2. Pre Excel 2007: Insert|Name|Define...|Names in Workbook:
Excel 2007: Formulas ribbon|Name Manager|New|Name:

3. Enter CelCol as the name

4. Refers to: =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

5. Confirm and close out of the Name dialog.

6. Put the colours you want to sum by in column D.

7. Enter the formulas shown and copy down.

Excel Workbook
	A	B	C	D	E	F	G
1	1	33			33	7
2	2	43			0	3
3	3	0			43	11
4	4	43
5	5	43
6	6	33
7
Sum by Cell Colour