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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

I just got an error when I tried it.

Thanks

Hi

The function code would be

Please Login or Register  to view this content.

rylo

Thank you so much for all your answers, time and patience!!