Summing coloured cells

[Dilly]

I am using excell 2003 and trying to sum a column by background colour.

My column has values in with two colour backgounds Green and Yellow. I am trying to create a total sum at the bottom of the column for each colour but cannot get it to work.

Any advice please?

 

[SkipVought]

Hi,

Out of curiosity, what are you trying?

What is the LOGIC for coloring a cell Green or Yellow?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

You cannot do this with native Excel functionality.

Either install ASAP utilities:

http://www.asap-utilities.com/download-asap-utilities.php

or write a user-defined function in VBA to do this ( or even use an old Excel4 macro command in a defined name ).

Which would you rather do?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Dilly]

Sorry cannot download any add ons as company network.

Basically the spreadsheet is not mine but the logic is that we have two Dept with a similar type of worker. We man work with these workers and it reflect by Green or Yellow which Dept they orginate from. We show these men required against differant work for a week down a column and are trying to sum each type of manning at the end of the column.

Hope this helps

 

[SkipVought]

Are you using Conditional Formatting to color the cells? If not, you should.

You might check out the SUMIF and COUNTIF functions.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dilly]

I cannot get the correct sytax for SUMIF for the color. No conditional formatitting, manual intervention. I am trying to keep it simple for the owner

 

[xlbo]

You can only do a sum based on colour by using a customer function (VBA) - see this thread for some details:
thread707-1379711

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

it reflect by Green or Yellow which Dept they orginate from

It is NOT SUMIF for COLOR.

It is SUMIF for Dept!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dilly]

Thanks for the help I will use the VBA code

 

[SkipVought]

VBA?

Do you have a column that contains Dept?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dilly]

Sorry no column containing Dept. As stated before this spreadsheet does not belong to me and I would probably redesign it. I can advise changes but they do not need to be accepted.

I am going with Xlbo information and modifying the information in the thread he has linked to.

The solution to this issue may be beyond the Excel knowledge of the owner of the spreadsheet, so redesign of spreadsheet may be a better option.

 

[kjv1611]

If the department code isn't listed, it would be a simple fix. Just add in a column with that information, and then they can still color code (via conditional formatting as Skip suggested) the items according to the value in the department code.

Really, if the document is important at all, and it sounds like it is, then it SHOULD be redone, so that it is done correctly, and not just left in it's current condition b/c "that's the way it's been done." That answer has been proven to fail. Basically if a better method is available, and the costs would not outweigh the benefits, then WHY NOT?!

If you go the VBA route, then if any changes are necessary, you'd be the one to maintain it, from what it sounds like. Whereas if you do it within the Workbook itself - since it does sound like a simple/easy fix - then practically anyone could make the necessary changes.

Anyway, that's just my thought on it.. I'd seriously consider going Skip's route on such a simple task as this one..

--

"If to err is human, then I must be some kind of human!" -Me

 

[GSCaupling]

No conditional formatitting, manual intervention.

I agree with KJV1611. Add a column for the department.

Instead of manually color-coding the employees, enter their department in a separate column. Use conditional formatting to color the employee names based on the department data entered. Then you can use [blue]SUMIF[/blue] to get the totals you want by using the same logic you used for the conditional formatting.

Good Luck!

GS

[red]******^*******
[small]I[/small] [small]Love[/small] [♥] [small]Redundancy.[/small][/red]