How to sum intermittent cells per cell fill color

[MrDB4Excel]

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]

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.

 

[Fluff]

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]

These are things I do not know how to do, but will look into it, thanks

 

[Fluff]

As an example, if you add this to a regular module in your workbook

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]

ADVERTISEMENT

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

 

[MrDB4Excel]

Yes, they contain the formula =EXTRACTNUMBERS(#) where # is the cell address.

 

[Fluff]

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]

Awesome Fluff, this is exactly what is needed. You the best

 

[Fluff]

Glad it worked & thanks for the feedback

 

[MrDB4Excel]

You Are Welcome - Пожалуйста - Nemáš zač