Sum conditional formatted cells

[wreckerp]

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]

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]

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]

@Joe4

The image is basically this

Book1
	S	T	U
11	6,232,667
12
13			829,547
14			50,163
15			53,303
16			38,374
17			249,420
18			364,074
19			429,538
20			474,598
21			487,730
22			39,950
23			375,260
24			242,750
25			361,000
26			541,420
27			431,133
28			861,550
29			211,000
30			443,671
Data

I have put the C8 value in S11 for convenience.

PS the board will be down tomorrow.

 

[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:

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]

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.