How to SUMIF (or ??) using criteria from another function value return?

Question

I am trying to do a very simple sum of a column that excludes the colored ones. The column I wish to sum is all my accounts and the green ones represent the paid accounts. I want a sum that represents the "left to pay" value to keep track of my progress without redoing my formula every time. The color is not conditional, nor can it be.

I have 2 functions created already:

Function GetColor(MyCell As Range)
GetColor = MyCell.Interior.ColorIndex
End Function

and

Function PAID(MyCell As Range) As Boolean
    If MyCell.Interior.ColorIndex = 50 Then
        PAID = True
    Else
        PAID = False
    End If
End Function

So I have already created one column next to my numbers that have the formula (with changing cell number):

=PAID(C13)

and this spits out TRUE or FALSE values that I can then based my SUMIF formula off of, currently I have this (E column containing values from the PAID function, C contains my account values):

=SUMIF(E2:E18,"FALSE",C2:C18)

I would like to see if it's possible to bypass making this extra column and run the function directly in the SUMIF (or maybe another function?) so that all I have to do is color my cell and refresh only one formula.

This is rather complex and open to errors to rely on hard colour-coding - why dont you enter in a simple "paid" against the paid items? — brettdj
I realize using the ColorIndex is not ideal but it did the job for a simple task such as a sum. Also I am challenged frequently in Python, XML, and VB despite not being a programmer and hating coding, so this keeps my brain active on that front. It's a neat idea for this spreadsheet anyhow and simpler than I thought to get as far as I did. — user3161018

Rusan Kax Rusan Kax · Accepted Answer · 2014-12-23T01:00:47

Using colours as part of the program decision process is not ideal, and is overly complex for a simple task.

But assuming you want this (or have no control over this), and the cells you want to sum are NOT coloured with Interior.ColorIndex = 50 - and assuming your values are in range C2:C18 (or wherever), a VBA function to do this is below.

Use the function as =PAID(C2:C18,50)

Function PAID(MyCells As Range, colour_avoid As Integer) As Double
Dim cc As Range
Dim accumulate As Double

accumulate = 0 'not needed but good practice

  For Each cc In MyCells
    If (cc.Interior.ColorIndex <> colour_avoid) Then
       accumulate = accumulate + cc.Value
    End If
  Next cc
PAID = accumulate
End Function

To repeat, this function will sum all the cells NOT coloured with a colour.index that you give to it (say, 50).

How to SUMIF (or ??) using criteria from another function value return?

1 Answers