SUMIF color=...

[JChandler22]

Right now, I have a SUMIF function that works fine:

=SUMIF(G12:G54,"SUBTOTAL",M12:M54)

I've never seen this before, but is there a way to build cell color into a formula? For instance, in the equation above, I would only want to sum the cells in M12:M54 that were NOT highlighted yellow.

Will I have to resort to VBA to do this?

Thanks in advance,
JChandler22

[dominicb]

Good afternoon JChandler22

You will have to resort to VBA to do this. Chip Pearson has a section devoted to this here :

http://www.cpearson.com/excel/colors.htm

Alternatively, you could donload my add-in via the link below which has a utility that will add the VBA code to your file for you (Ulitmate > Formulae > Add Function). The custom functions you would probably need would be CELLCOLOUR() and SUMCELLCOL(). The help file included gives full details of syntax.

HTH

DominicB

[JChandler22]

Thanks for the reply, Dominic.

I tried Chip's UDF, which would work well except for the fact that my colored cells are conditionally formatted, and the UDF doesn't pick up on that.

Here's my attachment:
example.zip

You should be able to follow what I'm trying to do when you open that and take a look. Keep in mind that although I've only shown three tasks in this example, I have about 160 in my actual xls. I've erased all the real data here, as well.

Thanks to anyone who can offer suggestions!

[JChandler22]

Ah, got it. A simple SUMIF function did the trick.