Sum column with only coloured cells

[thalt]

Hi All

I have a feeling that this is a dumb beginners question.

I have a worksheet containing a large column with random coloured (red) cells, how can I Sum only the coloured cells and then only the empty cells?

TIA thalt

[BigBas]

Excel doesnt have a built in function to do this, so you will need to use a user defined function. This is the code that I wrote to sum colored and un colored cells.

Enter this code into a general module. The function takes two arguments: the first argument is the Range, the second is TRUE or FALSE (True sums colored cells, FALSE sums uncolored only).

A formula would look like: =ADDCLRS(A1:A100,TRUE)

Code

Please Login or Register  to view this content.

[thalt]

Hi BigBas

I couldn't get the formula to work and I do not know how to use macros.

I will have to study up on how to use macros.

Any other formulas.

Thanks for the reply at least I know it wasn't a simple question. Thalt

[JBeaucaire]

This is a new function, not a macro. You have to add it into your Sheet as a usable function.

How to Create Excel User Defined Functions

1. Open up your workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet.
7. Use the functions (They will appear in the Paste Function dialog box, Shift+F3, under the "User Defined" category)

If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save your excel file that contains your VBA functions as an add-in file (.xla). Then load the add-in (Tools > Add-Ins...).

Warning! Be careful about using custom functions in spreadsheets that you need to share with others. If they don't have your add-in, the functions will not work when they use the spreadsheet. Best to store those UDFs in the sheets themselves.

(Source)
===========
BigBas already suggested how this new function would now work as a formula:

Please Login or Register  to view this content.

[thalt]

Hi BigBas & JBeaucaire

I have followed the directions and got the formula =AddClrs(T3358:T3581,"T") which gave me a zero, I retyped the "T" as TRUE and still got a zero.

As I had to deliver an answer I put a one in every red cell and the formula computed the number in column D.

As a test bench I did a small section in Column G but I could only get it to work if I put 1s in also. The 1s without a colour, and the colours without a 1, were not counted

Am I doing something wrong? thalt

[JBeaucaire]

I couldn't get AddClrs to work either. Here's another UDF called ColorFunction. Add it in a Module of its own.

Please Login or Register  to view this content.

You'll need to set a color reference cell to compare to. Go to AA1 and set it to the red color you're counting. Now this formula will work:

Please Login or Register  to view this content.

If you set the last parameter to TRUE, it will SUM the values in the cells that match the search color. Cool. I just tested this on your sheet and it works great.

FALSE = Count the number of cells that have a specific color background.
TRUE = Sum the contents of cells with the specific background color.

[thalt]

Thanks JBeaucaire
That works perfectly, as my column has over 5000entries you can see why I wanted a formulaic way of doing it.

Plus I have now fiddled with my first UDF so I am sure it will not be my last.

Thanks for both your help it is much appreciated. thalt

PS I cannot find the Solved button so I will have to return to get the directions to do it.

[JBeaucaire]

Edit your first post, then click the Prefix box to the left of the thread title.

[uranium]

Tried it but unfortunately, I just realized my version is in french and it is 2010... I will keep on searching!