Making Sum of cells that have or dont have a fill color

[nadermourad]

I don't know how to start. I googled a couple of things but they didn't work.

Basically i need two formulas.
1) formula that makes a sum of the selected cells that have no background fill
2) formula that makes a sum of selected cells that do have a background fill


Could anybody help me out please?


Much appreciated.


Nader

[jakeisbill]

Can You attach a sample worksheet?

[nadermourad]

I attached the file.
So right now i manually made a sum of the cells with a color fill and sum of the cells without color fill.
But now i need a function that does that automatically.

[royUK]

There is no inbuilt function, you need VBA to do this

[nadermourad]

Can you give me the VBA code then?

[royUK]

here's an example

[nadermourad]

Thanks a bunch for that!
Just one question:

What is this for :


Sub TestCount()
MsgBox ColourFunction(Range("a2"), Range("A2:a4"), 1)
End Sub
Sub testSum()
MsgBox ColourFunction(Range("a2"), Range("A2:a4"), 2)
End Sub

[nadermourad]

I'm so sorry to bother you but my Excel/VBA skills are pretty limited and i cant seem to understand the whole example

Could you delete the rest of the example so i can simply copy n paste whatever it is i need to use?
So i dont need the averages or count or stuff like that.
I simply need the sum of the color filled cells within the range of :T4 - T999999

How would my vba code look then?

[jbeardslee]

roy
This is great except when there are No Cells that color. In that case I need an Count of "0". instead I get #Value.
How can I change to code for ColourFunction2 to give teh correct count?