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
Hello Fluff,
I was a bit premature in saying this was exactly what was needed. I could not get it to work after much trial.
I finally arrived at the solution after considerable testing. Here it is in its entirety prefaced with some comment.

How I Finally Fixed This Problem
The first “A” cell that contains the formula =EXTRACTNUMBERS is A284; therefore the formula in this cell is =EXTRACTNUMBERS(C284). This formula looks at the information in the corresponding “C” column cell, in this case C284 which contains the following text: 9 Lines Translated Today.
This in turns provides the numeric value in A284 of 9. Then I developed 4 helper columns as follows:
These Four Helper Columns may or may not have all been needed, but at this point the end result works perfectly, so these 4 helper columns remain and under no circumstances will I endeavor to fix, alter or change in any way their content.
Column “J” contains the formula: =ISFORMULA(A284)
Column “K” contains the formula: =VALUE(A284)
Column “L” contains the formula: =IF(A284>K284,A284*1)
Column “M” contains the formula: =VALUE(L284)
Cell “M1” contains the formula: =SUMIF(M284:M1411,">0") - SUMIF(M284:M1411,"<0")
Cell “A11” which contains the running total for all the cells in column “A” below cell “A284” which keep track of the “Lines Translated Today” has the simple “=M1” statement.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,127,898
Messages
5,627,530
Members
416,250
Latest member
darius_rebelo

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