Sum conditional formatted cells

wreckerp

New Member
Joined
Oct 30, 2019
Messages
6
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!


 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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
Joined
Oct 30, 2019
Messages
6
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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
Joined
Jun 12, 2014
Messages
30,562
Office Version
365
Platform
Windows
@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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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
Joined
Oct 30, 2019
Messages
6
Joe, that is fantastic!

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
You are welcome!
Glad I was able to help.
:cool:
 

Forum statistics

Threads
1,078,491
Messages
5,340,683
Members
399,389
Latest member
JayNExcel

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top