Formula to Sum a Column by Fill Colour

S

stuartgb100

Active Member
Joined
May 10, 2015
Messages
322
Office Version
  1. 2021
Platform
  1. Windows
Hi,

I have col C containing numbers.
Some cells in col C have a pink fill colour RGB 252:228:214
Some cells in col C have a blue fill colour RGB 221:235:247

In cell H3 I'd like to place a formula that will be the total of all pink cells in col C.
Similarly in J3 a formula for the total of all blue cells in col C.

Can I do this please ?

For better presentation, I'd rather not use two columns (one for each colour).

Thanks.
 
Sort by date Sort by votes
He never said that in Post #1 0070]The OP has specifically stated that a VBA solution is not possible here.[/QUOTE]
 
Upvote 0
You can't use "color" in formula.
We can use the cells, rows on even / odd but the layout sheet, make it impossible.
If you delete those empty rows (where the two blue lines) then you can calculate a single formula, otherwise you have a chain of formulas ...
 
Upvote 0
Stuart - do I understand that pink are projected figures and blue are actual? And do I also understand correctly that projected numbers will always be on the row above the actual numbers? In which case, it is just a question of choosing the correct range to sum.

Excel 2016 (Windows) 32 bit
ABCDEFGHIJK
DETAILED OVERHEADS - PROJECTED & ACTUAL:v1.
Staff SalariesABC 1
ABC 2
New Employee(s)
Accounts
Wage Increases

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]COMPANY:[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Projected[/TD]
[TD="bgcolor: #F2DCDB, align: right"]0.00[/TD]
[TD="align: center"]Actual[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0.00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]OVERHEAD ELEMENT[/TD]
[TD="align: center"]SUB HEADING[/TD]
[TD="align: center"]ANNUAL[/TD]
[TD="align: right"][/TD]
[TD="align: center"]JAN[/TD]
[TD="align: center"]FEB[/TD]
[TD="align: center"]MARCH[/TD]
[TD="align: center"]APRIL[/TD]
[TD="align: center"]MAY[/TD]
[TD="align: center"]JUNE[/TD]
[TD="align: center"]JULY[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]TOTAL[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #F2DCDB, align: right"]48,000.00[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"]4,000.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]4,000.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]4,000.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]4,000.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]4,000.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]4,000.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]4,000.00[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #DCE6F1, align: right"]51,000.00[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"]4,000.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]4,000.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]4,000.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]4,000.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]4,000.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]4,000.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]4,500.00[/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #F2DCDB, align: right"]42,000.00[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"]3,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]3,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]3,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]3,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]3,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]3,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]3,500.00[/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #DCE6F1, align: right"]45,000.00[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"]3,500.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]3,500.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]3,500.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]3,500.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]3,500.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]3,500.00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]4,000.00[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #F2DCDB, align: right"]30,000.00[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"]2,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]2,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]2,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]2,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]2,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]2,500.00[/TD]
[TD="bgcolor: #F2DCDB, align: right"]2,500.00[/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]
[TD="bgcolor: #F2DCDB, align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]

</tbody>
Overheads 2017​
 
Last edited:
Upvote 0
Does this help?


Excel 2016 (Windows) 32 bit
GHIJ
3Projected120,000.00Actual96,000.00
Overheads 2017
Cell Formulas
RangeFormula
H3=SUMIF($B$8:$B$17,"<>",$C$8:$C$17)
J3=SUMIF($B$8:$B$17,"",$C$8:$C$17)
 
Upvote 0
Ali,

Sorry I did not get back to you earlier.

Yes your reasoning for the purpose of pink and blue cells was correct.

The formulae work perfectly !

Regards and thanks again.

ps: just round the corner from you (CB8) - small world sometimes.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
498
rahildhody
R
P
  • Question Question
Hoping to get help with a Macro!
Replies
0
Views
299
PEM000
P
R
  • Question Question
Help please - COUNTA error
Replies
1
Views
269
StephenCrump
S
S
  • Solved
Filling cells partially (based on percentage) and any colour of my choice
Replies
7
Views
1K
Peter_SSs
Peter_SSs
smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
233
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,226,832
Messages
6,193,211
Members
453,780
Latest member
Nguyentam2007

Share this page

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
Back
Top