Thread: IF function with 2 conditions

Is your cell color made by hand or by conditional formatting?

It's filled in by hand - I'm actually making a spreadsheet for my Pathfinder character - I've colored all the skills that are class skills green. You get a bonus for that, so I'm trying to make a formula that will identify if a skill is a class skill or not when you put ranks in it.


pathfinderExcel.jpg


The purpose of this spreadsheet is so I don't have to keep erasing and re-filling in little tiny boxes on the paper and end up with a huge mess, and I'll have enough room and spaces to fill in any extra info, and it will all update itself!

If you want me to upload a link to a file for a better look, I'll try to do that on dropbox or something.

Try this UDF function.

In some cell (in example A1) define color and minimum value, then use function =SumByColor($A$1;A3:O3)

Function SumByColor(CellColor As Range, SumRange As Range)
Dim myCell As Range
Dim iCol As Integer
Dim myTotal As Long
iCol = CellColor.Interior.ColorIndex ' get the target color
For Each myCell In SumRange ' lok at each cell in the designated range
    'if the cell color matches the target color
    If myCell.Interior.ColorIndex = iCol Then
            ' add the value in the cell to the total
        If myCell >= iCol Then
        myTotal = WorksheetFunction.Sum(myCell) + myTotal
    End If
    End If
Next myCell
SumByColor = myTotal
End Function

Copy above function
Go to excel, press Alt+F11
Paste it

Then go to the cell where you want to put formula (like any other formula), start writing =SUMBY... end you would be able to choose it.

Then select values as exampled above: $A$1 (or any other cell) where is defined your color you want to sum and minimum value and range you want to sum