SUM by Colour

[scott1971]

Guys,

I am trying to create a Holiday/Absence Sheet that will count up hours of Holidays, absence, business Visits etc etc.

The only way I can think of doing this is by colour and filling the cells with the Number of hours holidays etc but to do this I need to create a formula that will sum by colour. I've tried a variety of things and I'm sure this can be done using VBA but it's something I have never used so I am at a loss.

so, what I need is a SUM (A1:A31 if colour= Red) and SUM (A1:A31 if colour= Blue) etc etc.

I hope this makes sense or one of you geniuses out there can come up with somethign smart that works better than my idea!

Cheers

Scott

 

[xlbo]

bad bad bad idea

There is no native way to do anything by colour in excel

You are better off structuring your data in a different way - use letters rather than colours to differentiate different types of times



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

 

[scott1971]

xlbo

That is what I currently do, I use figures for holidays and Sum them and Use an A for Absence, a B for Business Days etc and count the A's and B's and it works fine but the Absence was previously only by Day and now I need to count it by hours (possibly even half hours) and I can't see a way to do this. I've tried putting two A's in the cell to signify 2 hours and then used the COUNTA function but that didnt' work.

Totally lost on this and to be honest it's been years since I used XL properly to any level so struggling even more!

Any other help or pointers would be great.

Cheers

Scott

 

[xlbo]

All depends on how you are STORING your data

I would STORE it (not report - store) in this kind of layout

Name Date Type Hours


It is then fairly trivial to use lookup formulae to populate this data into a report that shows different colours for different types of holidays / absence etc


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

 

[scott1971]

Unfortunately this is something the business use so I can only make some changes, they dont' want it torn apart.

It is a simple calender, a worksheet for each month then:

Name Day 1 Day 2 Day 3 etc Total Holiday Houors Total Absence Hours.

The users get a pick list where they pick the number of hours of Holiday or Absence.

sorry to go on with this but it's something I have to get done by the end of the week and it just isn't happening!

 

[xlbo]

they don't want it torn apart?

So they don't want something that works properly? They want something that is clunky, hard to manage and not easily changed?

Well - your call. If you don't want to take advice, go to the Forum707 and do a search for "Sum by colour" or "Sum by color"

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

 

[scott1971]

The way they see it is that it works now so it should continue to work and it's a tight timeframe to get this in.

The colours piece is possibly a red herring. I've tried to count letters, ie use one S for one hour, two S's for Two hours etc etc, that is what I currently do but it only counts days just now and when I try to count all the S's it doesn't work so I'm wondering if there is any workaround that way?

 

[xlbo]

Well it doesn't work now or you wouldn't be on here asking a question......

The way you have it laid out now is clunky and not the best way to do it. You are making a rod for your own back by continuing on with this methodology

The report itself doesn't need to change. As I was at pains to explain in my 1st post, you can change the way you STORE the data without changing the way it is DISPLAYED

Sounds to me like you are storing the data in the format you are displaying it. This is rarely if ever a good idea

Store in one sheet in a suitable storage format
Display in another sheet in the required display format



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

 

[AnotherHiggins]

I just want to back Geoff (xlbo) up here.

Management has every right to dictate how they view the data. But viewing/reporting data and storing historical data are two completely, totally different things.

You should argue for changing the data structure. You might not even realize it yet, but you really want to move to a proper structure, as Geoff laid out above.

And there are very good reasons that we call that the 'proper' format: If you want to use Excel's many powerful analysis and reporting tools, you need to store your data in a normalized table.

There are several other folks on this board who will tell you the same thing. Look down the list of MVPs over to the right side of the screen - most, if not all, of those people would agree with what xlbo has told you.

So I'd seriously suggest going back to management and telling them that you have consulted with some industry professionals with decades of combined experience and they all agree that going down this path is a terrible idea that will lead to many, many unnecessary man-hours in the future, all to maintain the poor design that they are dictating now. OK, you might want to change the way you say that, but be sure to mention how it will affect the bottom line.

Once again, you will want to bring down the multiple columns for each day into a single column.

As for the tight time frame - it shouldn't be a problem. Have a look at faq68-5287. I've used that FAQ to clean up the kind of data you're facing more than once.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[scott1971]

Guys

I appreciate your replies and what you are saying, with regards the timeframes, that is probably more down to me understanding what I need to do than anything else.

As I said in my original post, it's been a long time since I worked with Excel in any way at all and this is all a learning curve for me as well.

I'll have a play around with this and see what else I can do and revisit the Pivot Tables and try and work out there benefit in this case.

Cheers

Scott

 

[AnotherHiggins]

I still think you should change structure, but I also know what its like to be up against a deadline.

If you don't think you can clean things up in time, provide some example data in your current structure and tell us how you are hoping to sum things up.

There must be some logic dictating what fill color you would use for different cells. We might be able to use that same logic along with a SumProduct (or other formulas) to extract the data you're looking for, at least for the time being.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[scott1971]

I can't seem to upload the worksheet I've been playing with but I'll try and detail below what it does. I have a different worksheet for each month and users in rows then I sum at the end. (1st, 2nd, 3rd are the dates).

Name 1st 2nd 3rd Total Holidays Total Absence
Scott 7.5 S 3.75 11.25 1*

So, the Users select the hours of holidays they have had and I sum that quite simply with = SUM (B2:AF2). The Absence is defined with an S and I Count that using =COUNTIF(B2:AF2,"S"). From the Users point of view they have a "pick list" with all the Holiday hours available and the font and background are the same colour (blue) so as they can easily identify the holidays. Same with sick days. just now though Sick days are counted simply as days and it works fine but they now want to count it by hours(though they'd be happy not to count 1/4 or 1/2 hours).

Any help would be much appreciated and I'm happy to e-mail the document if that is of any help.

 

[ck1999]

Could you add a row above or below the header row to signify if the date is a holiday or not then use a countif statement to count hours in row 3 based off of if row 1 says it is a holiday.

Also you could use a userdefined function in vba (I Know thisi not the vba forum) and then put in sick days as "S, 4" in a cell then use a vba function to run through col b - af and if any cell starts with an s then use the right function to get the hours and sum up. If you want to pursue this please post in vba forum

ck1999