Sumif cell is certain color

[bridge4444]

Hi There,

Just wondering if there is away how to sumif with criteria being the cell colour?

Cheers

Ryan

[Chance2]

Found this code for a UDF...

Please Login or Register  to view this content.

You can use this code by adding it to a module in your workbook and then using the formula:
=SumByColor(sumrange,cellwithcolor)
Just format the cellwithcolor to the color you want to sum.

[bridge4444]

Is there away how to do this not involving VBA?

[Tony Valko]

If the color format is manually applied then this can not be done without the use of VBA.

In general, it's not a good idea to base calculations on cell formats.

If the color is applied using conditional formatting you should be able to build a formula based on the logic that's used to apply the CF.

[bridge4444]

Thanks Tony,

Yes it is based on a conditional formatting. The conditional format is as follows, if a formula is overwritten the cell changes colour. My conditional formatting formula is =G10<>IFERROR(('BU Template (LY)'!G10/SUM('BU Template (LY)'!$S$10:$S$121)*((Headsheet!$F$5-Headsheet!$F$9)/(1+Headsheet!$F$4))),0) and this conditional format is applied to the matrix G10:R121

Do you know how I can apply this to sumif? I am struggling with the sumif 'criteria' alters depending on the cell reference, i.e. the formula above is only true for cell G10

[Tony Valko]

Maybe this array formula**:

=SUM(IF(G10:R121<>IFERROR(('BU Template (LY)'!G10/SUM('BU Template (LY)'!$S$10:$S$121)*((Headsheet!$F$5-Headsheet!$F$9)/(1+Headsheet!$F$4))),0),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[bridge4444]

Thanks for the reply Tony,

I think it is definitely on the right track, the formula I have now is

=SUM(IF('BU Template'!G10:R121<>IFERROR(('BU Template (LY)'!G10:R121/SUM('BU Template (LY)'!$S$10:$S$121)*((Headsheet!$F$5-Headsheet!$F$9)/(1+Headsheet!$F$4))),0),1)), However this is not quite working as only counts the number of cells that have been overwritten (changed colour) How do I amend this to sum the cells that have been amended???

Cheers for your help so far

[bridge4444]

Hey Thanks for your help, Have managed to sort it

[Tony Valko]

Good deal. Thanks for the feedback!