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

Excel 2013/2016
	A	E
1	Store
2	1	33
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
Tmp

Cell Formulas
Range	Formula
E2	=SumColour(A2:A16,A7)

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]

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]

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č