Sum Coloured Cells

[Sarah T Cuttings]

I have a spreadsheet with coloured rows as below. The values in Column E are the total values of jobs, the colour, represents a region as per my snip of Cuttings London, Cuttings East & Cuttings West. The row with the £26,777 is row 96, column E.

What I would like to do is total all values as per their colours and if I chose to change the colour, the total value will update with the change.

Is this possible?

Thanks
Sarah

 

[jkpieterse]

It is far easier to add a column with the region information and then use SUMIFS. You can then use a conditional format to automagically apply the coloring too.

 

[CoffeeCup]

JK is right but for fun it can be done
read : https://www.exceldemy.com/how-do-i-sum-colored-cells-in-excel/

 

[jkpieterse]

There is a snag with that link: the examples that use GET.CELL and the VBA UDF may deliver unexpected results because they both use the pre-Excel 2007 color index, which is limiter to 56 pre-defined colors. You may be adding similar looking colors as if they are the same.

 

[jkpieterse]

Here is a screenshot of the standard color palette applied to the Excel grid. In each cell I've added the ColorIndex. As you see there are many colors which have the same color index

 

[CoffeeCup]

JK - that i did not realize
bedankt another lesson learned.
this one goes into my memo- file

 

[Sam_D_Ben]

Another cheapest method is assigning a number in the helper column (H96:H111) that corresponds to the color of that row

Book1kkkkgfgdesd.xlsm
	E	F	G	H
96	26777.00	Trevelyan Middle School, Windsor, Berkshire	48.1	1
97	716.00	UKPN Substation Butler WTW Aylesford Kent	58.49
98	870.00	The Pyramid Centre, Southsea Hampshire	49.25	2
99	1545.00	HM Portsmouth Naval Base, Hampshire	67.74	2
100	890.92	North West Cambridge AT VO, Cambridgeshire	45.5	2
101	3002.00	Yatton Primary School, Bristol, Avon	44.18	2
102	530.00	Westhorpe Gardens Bonding, London NW4	54.03	1
103	49920.00	RAF Lakenheath Task Order 6.2 Brandon Suffolk	43.97
104	1990.00	7 Princes Street, London EC2	49.64	1
105	585.00	Brettenham House, Lancaster Place, London WC2	46.58	1
106	20000.00	Cambridge South Police Station, Milton, Cambs	43.26
107	7230.00	Farm Lane, Fulham, London SW6	58.99	1
108	600.00	Harlow Transport Hub LP Design, Essex	90.29
109	16588.00	University of Southampton New Building, Hampshire	47.8	2
110	13041.03	Project Icon Phase 2, B1 3 Stevenage Hertfordshire	45.66	1
111	435.28	Devonshire Gardens Material Supply, Cambridge	54.48
112
113	144720.23		53.00
114
115
116	Cutings London	50153.03
117	Cuttings East
118	Cuttings West	22895.92
Sheet1

Cell Formulas
Range	Formula
E113	E113	=SUM(E96:E111)
F116	F116	=SUMIFS($E$96:$E$111, $H$96:$H$111, 1)
F118	F118	=SUMIFS($E$96:$E$111, $H$96:$H$111, 2)

 

[Sarah T Cuttings]

Thanks both. I opted for the VBA - method 4 in the web link and all is working well.

Thanks
Sarah

 

[jkpieterse]

As long as you're aware of the limitations. To be on the safer side, replace "ColorIndex" in that VBA code with "Color". It will then use the RGB values which makes it far less likely that different colors are counted as same

 

[Sarah T Cuttings]

Ok JK, I'll change the code as mentioned. Thanks for the heads up.