# Sum conditional formatted cells

#### wreckerp

##### New Member
Hi all,

I am trying to sum all of the green cells (see picture @ https://ibb.co/vjVRrBC). Green cells are formatted using a conditional formatting formula rule.

The purpose of the formating is to highlight every cells in columns U that I have room for in my budget (C8) starting from the top (priority 1). If the cell is in green, that means I can make that purchase.

I've tried to use SUMIF using the conditional formatting formula but was only getting 0.

Thanks in advance for the help!

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Joe4

The SUMIF formula cannot work off of formatting, but if your Conditional Formatting is working off of a formula, we may be able to use that formula in our SUMIF (or SUMIFS or SUMPRODUCT) formula.
What exactly is your Conditional Formatting formula?
Note that I cannot see/download your image, so I am at the mercy of whatever information you can share with me here. So understanding your data layout and CF formula would be helpful.

#### wreckerp

##### New Member
Hi Joe,

Apologies for the image issue. Try this link see if it works https://imgur.com/OmAionS

My exact conditional formula is : =AND(SUM(U\$13:U13)<=\$C\$8,ISNUMBER(U13))

Cell C8 is a number (budget).
Column U shows the price of different items (data starts at U13)
The formula highlights all the items that I can purchase based on my budget and starting at column U13.
It start specifically from U13 because the items are sorted by priority.

Let me know if that makes things a bit clearer or if you would like more information.

Thanks for the help!

#### Joe4

The image issue isn't anything that you have done, it is that my work place blocks all file sharing and image sites.
I think this is something that I need to see what the data looks like to get a fuller understanding of what is going on.

So, it may have to wait until I am at a computer where I can see that (may not be until tomorrow). Note that there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

#### Fluff

##### MrExcel MVP, Moderator
@Joe4

The image is basically this

<b></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 /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">6,232,667</td><td style="text-align: right;;"></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;;"></td><td style="text-align: right;;"></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;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">829,547</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">50,163</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">53,303</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">38,374</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">249,420</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">364,074</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">429,538</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">474,598</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">487,730</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">39,950</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">375,260</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">242,750</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">361,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">541,420</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">431,133</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #76933C;;">861,550</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">211,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">443,671</td></tr></tbody></table><p style="width:3.2em;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)">Data</p><br /><br />

I have put the C8 value in S11 for convenience.

PS the board will be down tomorrow.

Last edited:

#### Joe4

OK, I see the issue now.
It is a running sum, so the range in your CF formula is changing with each row. That certainly makes it a bit trickier.
I will have to think about that. I know I can do it with VBA, but cannot think of a formula solution, off the top of my head.

#### Joe4

I am sure that there is probably a formulaic approach to doing this, but I am afraid it may be beyond my formula skills (I am thinking it may be an array formula).
However, if using VBA is acceptable, we can create our own function using VBA to do this.

Simply create a new blank module in the VB Editor in this workbook, and copy and paste this code there:
Code:
``````Function MaxSum(rng As Range, num As Double) As Double
'   rng = range of numbers to check
'   num = sum not to exceed

Application.Volatile

Dim cell As Range
Dim temp As Double

For Each cell In rng
If temp + cell.Value <= num Then
temp = temp + cell.Value
Else
Exit For
End If
Next cell

MaxSum = temp

End Function``````
Then you can use this like any other Excel function, i.e.
=MaxSum(U13:U30,S11)

#### wreckerp

##### New Member
Joe, that is fantastic!

Everythings seems to work as intended. Thanks for being so helpful, I really appreciate it!

#### Joe4

You are welcome!
Glad I was able to help.