How to sum intermittent cells per cell fill color

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
I have been searching for the better part of 3 hours to find out how to do this, but nothing seems to give an answer that works.
I have a sheet where I want to get a sum of the numeric values in the cells that contain fill colour index #46 (kind of a burnt orange). These cells are intermittent, meaning the first such cell is A284, the next is A311, etc., etc.
I want the sum to appear in Cell A11 which is a cell in my header.
Obviously, I could do this the long drawn out method as in =sum(a284,a311 etc.), adding the next generated such cell as it happens, but I already have many like this and as I continue working in this sheet I will have many more.
This is not a macro-enabled sheet and I do not want it to become macro-enabled, but to remain a simple *.xlsx workbook.
Any help is greatly appreciated.
I have tried SUMIF, setting conditional formatting, and a slew of other attempts at various other functions, but nothing is getting it right.
I obviously am missing something.
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
Thanks for trying. The fact you never heard of =EXTRACTNUMBERS function available in Kutools tells me there must be something available somehow somewhere to make this work.
I don't give up to easily and if I find something I will let you know.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
I have never heard of that function, because I do not use the kutools add-in. I simply create my own macros & functions.
It can be done quite easily with a UDF, but that means you either have to make the workbook an xlsm file, which you don't want to do, or create your own macro enabled add-in file.
If you will be the only person using this, you could also add a UDF to your personal.xlsb file
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
These are things I do not know how to do, but will look into it, thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
As an example, if you add this to a regular module in your workbook
Code:
Function SumColour(Rng As Range, ClrRng As Range) As Long
   Dim Cl As Range
   For Each Cl In Rng
      If Cl.Interior.Color = ClrRng.Interior.Color Then
         SumColour = SumColour + Cl.Value
      End If
   Next Cl
End Function
You can the use this in the sheet like

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Store</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">33</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;background-color: #FAB067;;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;background-color: #FAB067;;">6</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;background-color: #8DC182;;">8</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;background-color: #FAB067;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;background-color: #8DC182;;">12</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;background-color: #FAB067;;">14</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Tmp</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=SumColour(<font color="Blue">A2:A16,A7</font>)</td></tr></tbody></table></td></tr></table><br />

But you will need to save the workbook as macro enabled.
To install/use macros have a look here https://www.contextures.com/xlvba01.html
=Count
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another thought, are the coloured cells the only cells in col A that contain a formula?
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
Yes, they contain the formula =EXTRACTNUMBERS(#) where # is the cell address.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case it can be done with the help of a "helper" column.
In an unused column enter
=ISFORMULA(A1)
and fill down and then you can use
=SUMIF(D:D,TRUE,A:A)
change the D:D to refer to the "helper" column.
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
Awesome Fluff, this is exactly what is needed. You the best
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
Glad it worked & thanks for the feedback
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
You Are Welcome - Пожалуйста - Nemáš zač
 

Watch MrExcel Video

Forum statistics

Threads
1,127,584
Messages
5,625,643
Members
416,124
Latest member
DeMoNloK

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
Top