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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,511
Office Version
  1. 365
Platform
  1. Windows
A formula cannot detect the colour of a cell, so unless there is some rule as to which cells are coloured, you'll have to do it long hand.
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
Okay... So what might a rule look like that would pertain to intermittent cells?
Such a rule may be relevant to the colour index #46 , or a cell style, or a font style or colour OR What else????
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,511
Office Version
  1. 365
Platform
  1. Windows
Formulae cannot see any sort of format, so what determines if a cell is coloured?
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You're asking me?
I do not have the vaguest notion.
The only thing that comes to mind is for example in Conditional Formatting if a rule is set to "Use a formula to determine which cells to format" then maybe some kind of outcome to this could populate another cell that could be used.
Still, I am not even sure about that. I guess this is why I came to Mr. Excel in the first place to hopefully discover something from someone a whole lot more versed in Excel than I am.
Good luck
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,511
Office Version
  1. 365
Platform
  1. Windows
You're asking me?
Yes I am asking you, because I have no idea what your sheet looks like, or why some cells are coloured.
If you cannot tell why certain cells get coloured, then I cannot help.
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

So how do I enable you to see my worksheet?
I see no way to attach any file.
Basically, I use a lot of cell styles, font styles, fill colours throughout this workbook to enable visual understanding of its contents.
So until I can attach a file there is no way visually for you to understand what and why I do what I do to this workbook.
My initial thought was to use fill colour for these cells that do this: =EXTRACTNUMBERS(C602)
Each cell location that this formula exists in is getting information from its corresponding cell, in this case from C602, "14 Lines Translated Today"; thus in cell A602, which has fill colour index #46 , it displays the numeric value 14.
Then I was hoping I could use the cell colour to get a sum of all cells with this cell colour.
Hope this helps, but unfortunately, it would be much clearer if I could send the worksheet for you to view.
Why doesn't MrExcel allow attaching files to clarify?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,511
Office Version
  1. 365
Platform
  1. Windows
What is =EXTRACTNUMBERS(C602) I have never seen or heard of it before?
How is A602 getting coloured, is it manually of via conditional Formatting?

There are various reasons why this site does not allow for uploading of files, such as security, bandwidth, storage space etc, etc. and that is unlikely to change.
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
=EXTRACTNUMBERS is a function available in Kutools. It simply extracts a numeric value from a specific cell.
Daily as I translate words, phrases or sentences from English to another language I keep track of how many lines in the XML file I am translating.
These cells are getting coloured manually via a cell style.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,511
Office Version
  1. 365
Platform
  1. Windows
In that case without using VBA, I think your only option is to do it "Long hand"
 

Watch MrExcel Video

Forum statistics

Threads
1,129,445
Messages
5,636,322
Members
416,912
Latest member
danluk12

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