Sum Coloured Cells

Sarah T Cuttings

New Member
Joined
Oct 23, 2024
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.

1738933514914.png


1738933545713.png


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
 

Attachments

  • 1738933529006.png
    1738933529006.png
    1.8 KB · Views: 3
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.
 
Upvote 0
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.
 
Upvote 0
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
1738937493602.png
 
Upvote 0
JK - that i did not realize :)
bedankt :) another lesson learned.
this one goes into my memo- file ;)
 
Upvote 0
Another cheapest method is assigning a number in the helper column (H96:H111) that corresponds to the color of that row :biggrin:
Book1kkkkgfgdesd.xlsm
EFGH
9626777.00 Trevelyan Middle School, Windsor, Berkshire48.11
97716.00 UKPN Substation Butler WTW Aylesford Kent58.49
98870.00 The Pyramid Centre, Southsea Hampshire 49.252
991545.00 HM Portsmouth Naval Base, Hampshire 67.742
100890.92 North West Cambridge AT VO, Cambridgeshire 45.52
1013002.00 Yatton Primary School, Bristol, Avon44.182
102530.00 Westhorpe Gardens Bonding, London NW4 54.031
10349920.00 RAF Lakenheath Task Order 6.2 Brandon Suffolk43.97
1041990.00 7 Princes Street, London EC249.641
105585.00 Brettenham House, Lancaster Place, London WC2 46.581
10620000.00 Cambridge South Police Station, Milton, Cambs43.26
1077230.00 Farm Lane, Fulham, London SW6 58.991
108600.00 Harlow Transport Hub LP Design, Essex 90.29
10916588.00 University of Southampton New Building, Hampshire 47.82
11013041.03 Project Icon Phase 2, B1 3 Stevenage Hertfordshire 45.661
111435.28 Devonshire Gardens Material Supply, Cambridge54.48
112
113144720.2353.00
114
115
116Cutings London50153.03
117Cuttings East
118Cuttings West22895.92
Sheet1
Cell Formulas
RangeFormula
E113E113=SUM(E96:E111)
F116F116=SUMIFS($E$96:$E$111, $H$96:$H$111, 1)
F118F118=SUMIFS($E$96:$E$111, $H$96:$H$111, 2)
 
Upvote 0
Thanks both. I opted for the VBA - method 4 in the web link and all is working well.

Thanks
Sarah
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top