Create 2 Totals, One Based On Name And One Based On Name And Cell Fill Color

I have a formula, in my attatchment (= SUMIF($B$7:$B$85,N89,$C$7:$C$85) that totals all numbers in one column associated with a name "JIM" (N89) in another column (formula found in cell B89).

I also created a formula (=ColorFunction($A$8,$C$7:$C$85,TRUE) in cell C89 that adds everything in a specific range with same the fill color of cell (A8).

What I need is a formula or VBE code that will total all entries for the name "JIM" (found in cell N89) that have the same fill color as cell A8.
Thanks so much for your time.

Hi

This should give you the idea.

Function ColorAndName(ColRng As Range, NameRng As Range, Nme As String, coloffset As Integer)
  lcolor = ColRng.Interior.ColorIndex
  total = 0
  For Each ce In NameRng
    If ce.Interior.ColorIndex = lcolor And ce.Value = Nme Then
      total = total + ce.Offset(0, coloffset)
    End If
  Next ce
  ColorAndName = total
End Function

ColRng is the cell that has the color you want to match (say B4)
NameRng is the range that has the names (B4:B13)
Nme is the cell (or string) that has the name you want to check (N86)
coloffset is the offset from the name column that has the numbers you want to sum. So if you want to sum column C, then it would be 1).

In your example workbook put in the formula
=colorandname(B4,B4:B13,N86,1)
and it should return 13.42.

BTW, how did you make your function take the case of your UDF in the workbook???

rylo

Hi

1) OK, doesn't matter.

2) Enter the line

application.volatile

as the first line in the function. It won't update when you change the font color, but it will update on F9, or whenever there is a recalc in the worksheet.

3) Can you please remember to wrap any code in your posts. I've edited your post for you this time.


rylo

Rylo,

Sorry I am new at all of this (obviously), I will wrap my code next time. What do you mean exactly by enter the line

application.volatile

I just got an error when I tried it.

Thanks

Hi

The function code would be

Function ColorAndName(ColRng As Range, NameRng As Range, Nme As String, coloffset As Integer)
  application.volatile
  lcolor = ColRng.Interior.ColorIndex
  total = 0
  For Each ce In NameRng
    If ce.Interior.ColorIndex = lcolor And ce.Value = Nme Then
      total = total + ce.Offset(0, coloffset)
    End If
  Next ce
  ColorAndName = total
End Function

rylo