Adding sums in rows and columns and colouring cells with conditions

Hi all,
I have a long table where i need to add and colour individual cells
based on 'days home' and 'days away'.

The table is uploaded here
https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ

In order to automate some of this i'd like to automate some, but
preferably all aspects of this, such as:
1. automatically colour the cell orange for "home" and yellow for
"away"
2. add separately in the home and away columns the number of days for
each
3. add the rows for the alternate lines (ie days only, rates only)

I want to do this without yet another row so i thought that if i could
add an "a" and an "h" after the number of days in the cell i could set
up a conditional format and a sumif, but i simply could not get this
to work!!

Is there another way...?

I hope my problem is clear and would appreciate pointers to move ahead
- and i hope it can be achieved simply in excel without getting into
complicated vba etc as i am not a professional.

thanks in advance.

best
manosh
0
manoshde (24)
6/3/2009 5:28:01 PM
excel 39879 articles. 2 followers. Follow

4 Replies
423 Views

Similar Articles

[PageSpeed] 23

Hi,

I haven't looked at your data but 

1.  To sum based on the text "days home" you would use something of the form
=SUMIF(A1:A100,"days home",B1:B100)  
where column A contains the text days home or away and column B contains the 
data you want to sum.
2.  To format the cells based on the text you should try the Format, 
Condtional Format command. 

I did take a look at your sample SS and there is no clear way to destinguish 
the days home and days away.  Will they always be in the same columns?
-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Manosh" wrote:

> Hi all,
> I have a long table where i need to add and colour individual cells
> based on 'days home' and 'days away'.
> 
> The table is uploaded here
> https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ
> 
> In order to automate some of this i'd like to automate some, but
> preferably all aspects of this, such as:
> 1. automatically colour the cell orange for "home" and yellow for
> "away"
> 2. add separately in the home and away columns the number of days for
> each
> 3. add the rows for the alternate lines (ie days only, rates only)
> 
> I want to do this without yet another row so i thought that if i could
> add an "a" and an "h" after the number of days in the cell i could set
> up a conditional format and a sumif, but i simply could not get this
> to work!!
> 
> Is there another way...?
> 
> I hope my problem is clear and would appreciate pointers to move ahead
> - and i hope it can be achieved simply in excel without getting into
> complicated vba etc as i am not a professional.
> 
> thanks in advance.
> 
> best
> manosh
> 
0
6/4/2009 5:02:01 AM
On 4 June, 09:02, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> Hi,
>
> I haven't looked at your data but
>
> 1. =A0To sum based on the text "days home" you would use something of the=
 form
> =3DSUMIF(A1:A100,"days home",B1:B100) =A0
> where column A contains the text days home or away and column B contains =
the
> data you want to sum.
> 2. =A0To format the cells based on the text you should try the Format,
> Condtional Format command.
>
> I did take a look at your sample SS and there is no clear way to destingu=
ish
> the days home and days away. =A0Will they always be in the same columns?
> --
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
>
> "Manosh" wrote:
> > Hi all,
> > I have a long table where i need to add and colour individual cells
> > based on 'days home' and 'days away'.
>
> > The table is uploaded here
> >https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ
>
> > In order to automate some of this i'd like to automate some, but
> > preferably all aspects of this, such as:
> > 1. automatically colour the cell orange for "home" and yellow for
> > "away"
> > 2. add separately in the home and away columns the number of days for
> > each
> > 3. add the rows for the alternate lines (ie days only, rates only)
>
> > I want to do this without yet another row so i thought that if i could
> > add an "a" and an "h" after the number of days in the cell i could set
> > up a conditional format and a sumif, but i simply could not get this
> > to work!!
>
> > Is there another way...?
>
> > I hope my problem is clear and would appreciate pointers to move ahead
> > - and i hope it can be achieved simply in excel without getting into
> > complicated vba etc as i am not a professional.
>
> > thanks in advance.
>
> > best
> > manosh- Hide quoted text -
>
> - Show quoted text -

Thanks for your response shane.
unfortunately i had planned to have days home, and days away in the
same cell, distinguished by and "h" or "a" appended to the number.
i managed to get the conditional format to color the cell (by using if
(right(A1),len(a1)-1)) as well as use a array formula to add away and
home days in the right most columns.
the challenge that then remained was to sum in the individual columns
the man days, and unfortunately i could not figure this out.
so i've now added a row with H or A and am using sum ifs!
cheers
m
0
manoshde (24)
6/4/2009 11:54:32 AM
Well I don't know exactly what the cells contain but let's suppose it reads

15 a
12 h
4 h
3 a
.....

and assume these are in A1:A5, then the basic formula is

=SUMPRODUCT(--LEFT(A1:A5,FIND(" ",A1:A5)),--(RIGHT(A1:A5)="h"))

similarly for away, just change h to a.

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"manoshde@gmail.com" wrote:

> On 4 June, 09:02, Shane Devenshire
> <ShaneDevensh...@discussions.microsoft.com> wrote:
> > Hi,
> >
> > I haven't looked at your data but
> >
> > 1.  To sum based on the text "days home" you would use something of the form
> > =SUMIF(A1:A100,"days home",B1:B100)  
> > where column A contains the text days home or away and column B contains the
> > data you want to sum.
> > 2.  To format the cells based on the text you should try the Format,
> > Condtional Format command.
> >
> > I did take a look at your sample SS and there is no clear way to destinguish
> > the days home and days away.  Will they always be in the same columns?
> > --
> > If this helps, please click the Yes button.
> >
> > Cheers,
> > Shane Devenshire
> >
> >
> >
> > "Manosh" wrote:
> > > Hi all,
> > > I have a long table where i need to add and colour individual cells
> > > based on 'days home' and 'days away'.
> >
> > > The table is uploaded here
> > >https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ
> >
> > > In order to automate some of this i'd like to automate some, but
> > > preferably all aspects of this, such as:
> > > 1. automatically colour the cell orange for "home" and yellow for
> > > "away"
> > > 2. add separately in the home and away columns the number of days for
> > > each
> > > 3. add the rows for the alternate lines (ie days only, rates only)
> >
> > > I want to do this without yet another row so i thought that if i could
> > > add an "a" and an "h" after the number of days in the cell i could set
> > > up a conditional format and a sumif, but i simply could not get this
> > > to work!!
> >
> > > Is there another way...?
> >
> > > I hope my problem is clear and would appreciate pointers to move ahead
> > > - and i hope it can be achieved simply in excel without getting into
> > > complicated vba etc as i am not a professional.
> >
> > > thanks in advance.
> >
> > > best
> > > manosh- Hide quoted text -
> >
> > - Show quoted text -
> 
> Thanks for your response shane.
> unfortunately i had planned to have days home, and days away in the
> same cell, distinguished by and "h" or "a" appended to the number.
> i managed to get the conditional format to color the cell (by using if
> (right(A1),len(a1)-1)) as well as use a array formula to add away and
> home days in the right most columns.
> the challenge that then remained was to sum in the individual columns
> the man days, and unfortunately i could not figure this out.
> so i've now added a row with H or A and am using sum ifs!
> cheers
> m
> 
0
6/4/2009 5:12:01 PM
On Jun 4, 9:12=A0pm, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> Well I don't know exactly what the cells contain but let's suppose it rea=
ds
>
> 15 a
> 12 h
> 4 h
> 3 a
> ....
>
> and assume these are in A1:A5, then the basic formula is
>
> =3DSUMPRODUCT(--LEFT(A1:A5,FIND(" ",A1:A5)),--(RIGHT(A1:A5)=3D"h"))
>
> similarly for away, just change h to a.
>
> --
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
>
> "manos...@gmail.com" wrote:
> > On 4 June, 09:02, Shane Devenshire
> > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > Hi,
>
> > > I haven't looked at your data but
>
> > > 1. =A0To sum based on the text "days home" you would use something of=
 the form
> > > =3DSUMIF(A1:A100,"days home",B1:B100) =A0
> > > where column A contains the text days home or away and column B conta=
ins the
> > > data you want to sum.
> > > 2. =A0To format the cells based on the text you should try the Format=
,
> > > Condtional Format command.
>
> > > I did take a look at your sample SS and there is no clear way to dest=
inguish
> > > the days home and days away. =A0Will they always be in the same colum=
ns?
> > > --
> > > If this helps, please click the Yes button.
>
> > > Cheers,
> > > Shane Devenshire
>
> > > "Manosh" wrote:
> > > > Hi all,
> > > > I have a long table where i need to add and colour individual cells
> > > > based on 'days home' and 'days away'.
>
> > > > The table is uploaded here
> > > >https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ
>
> > > > In order to automate some of this i'd like to automate some, but
> > > > preferably all aspects of this, such as:
> > > > 1. automatically colour the cell orange for "home" and yellow for
> > > > "away"
> > > > 2. add separately in the home and away columns the number of days f=
or
> > > > each
> > > > 3. add the rows for the alternate lines (ie days only, rates only)
>
> > > > I want to do this without yet another row so i thought that if i co=
uld
> > > > add an "a" and an "h" after the number of days in the cell i could =
set
> > > > up a conditional format and a sumif, but i simply could not get thi=
s
> > > > to work!!
>
> > > > Is there another way...?
>
> > > > I hope my problem is clear and would appreciate pointers to move ah=
ead
> > > > - and i hope it can be achieved simply in excel without getting int=
o
> > > > complicated vba etc as i am not a professional.
>
> > > > thanks in advance.
>
> > > > best
> > > > manosh- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks for your response shane.
> > unfortunately i had planned to have days home, and days away in the
> > same cell, distinguished by and "h" or "a" appended to the number.
> > i managed to get the conditional format to color the cell (by using if
> > (right(A1),len(a1)-1)) as well as use a array formula to add away and
> > home days in the right most columns.
> > the challenge that then remained was to sum in the individual columns
> > the man days, and unfortunately i could not figure this out.
> > so i've now added a row with H or A and am using sum ifs!
> > cheers
> > m- Hide quoted text -
>
> - Show quoted text -

thats cool- i can get this to work!
cheers.
ps where is the Yes button?
0
manoshde (24)
6/5/2009 2:48:02 AM
Reply:

Similar Artilces:

Re: Extracting Exchange User Data From AD 02-24-10
If you do not like scripting, you can try GAL Exporter or Fast User Manager & Reports from IMIBO - http://www.imibo.com > > "Ringholz, Blake" <bringholz@nospam.com> wrote in message > news:76AEFC2F-85A7-4666-8262-27FB0737D09A@microsoft.com... >> Hello All - >> >> I need to get an Excel Spreadsheet that lists everyone first name, last >> name, email address, job title, etc pulled from Active Directory. Is >> there an easy way to do this? >> >> Thanks, >> Blake > > > > > ...

Inserting a Row from a UserForm
Hi guys- I'm kind of a self-taught VBA programmer and I can usually blunder around and get a macro to work, but I'm having trouble with a rather difficult one right now. I've created a UserForm that has 6 fields the user has to fill in for a database. Once the user hits the "OK" button, I want the macro to look for the last entry in the database that is identical to what the user filled in Field 1 of the UserForm and insert a row beneath it and fill in the cells with the data from the UserForm. So, i.e: Apple Apple Orange Orange Orange Banana Grape Grape If I type &...

Conditional charts
Is there a way to change the color of a chart bar based on the value of the cell that generates that particular bar? In other words, I have a chart with 15 vertical bars and I want to change the color of one or more of the bars if its value goes above 100% (but not all of the bars). JWeinberg Jon Peltier has instructions for conditional charts: http://www.peltiertech.com/Excel/Charts/format.html#CondChart Jeff wrote: > Is there a way to change the color of a chart bar based on the value of the > cell that generates that particular bar? In other words, I have a chart > wi...

Combining multiple rows with duplicate info -- help!
All, Is there any way to capture this data and consolidate the headers in Excel 2007. I would like to go from this: firstName joe lastName smith email jsmith@none.com title Other organization ABC Org industry Energy & Resources firstName tom lastName smith email tsmith@none.com title Other organization GCI industry Technology, Media & Telecommunications To this: firstName lastName email title organization industry joe smith jsmith@none.com Other ABC Org Energy & Resources tom smith tsmith@none.com Other GCI Technology, Media & Telecommunicatio...

Excel 2002
Have several spreadsheet files I use routinely. Three have recently crashed after I added another sheet. In each case the document recovery created a file missing all the color and text formats that the file contained before the crash. Not sure what other changes may have occurred. Is there something wrong with the copy of Excel on my PC? Could these three files be corrupt? Is there a procedure to "clean-up" these files? Thanks in advance for any suggestions. Mark Hi sounds like they are corrupted. I would suggest to copy the data + formats to a new, 'fresh' workbook. ...

adding a leading 0 to only the numbers with 4 digits
I'm converting a bunch of data and during the import Excel dropped the leading zero from all the lip codes. Since this is about 15,000 records, I'd like a way to add a leading zero to only the zip codes with 4 characters. Any ideas? thanks Swamp, Format/Cells/Special/Zip Code Beege "SwampYankee" <johndillworth@gmail.com> wrote in message news:1147451549.294898.147230@v46g2000cwv.googlegroups.com... > I'm converting a bunch of data and during the import Excel dropped the > leading zero from all the lip codes. Since this is about 15,000 > records, I&...

Wrapping text in a cell
In a single cell, suppose I want text to appear on two lines. Viz: Case One Case Two How do I do that so that I specify the wrap point? Thanks! If you are typing the data into the cell use Alt-Enter between each string to indicate where you want a line break to occur. Case One<Alt-Enter>Case Two Alt + Enter -- Lilliabeth ------------------------------------------------------------------------ Lilliabeth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27741 View this thread: http://www.excelforum.com/showthread.php?threadid=476428 If you...

Locking Formulas to Cells
I don't know if I am using the correct terminology but this is what want to do: I have placed formulas in multiple columns that calculat my sales numbers for a bid. The problem I am running into is that change the bids for every person and when I clear a cell is clears th formula from it as well. Is there a way for me to clear cells withou deleting the formula I have placed inside it? I know that I can jus grab the first cell in the column and drag it down to re-load th formula in that column but I don't want to have to do that. I want th formulas permenant and the data I enter...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

How do I add a hyperlink to an individual word in an Excel cell?
I am using Excel 2000 (not by choice) and I need to add a hyperlink to an individual word within the cell, not the whole cell itself. For example in the sentence "Click here or here to go to the appropriate web page." I want the words "here" to each have a separate hyperlink. Any ideas? I can manage some VBA too if necessary. Thanks, Rosalie Hi Rosalie, You can't do that in Excel. You would have to use HTML or Word or some other means. You could fake it, the entire cell would be a link, but you could after assigning the hyperlink select another...

how can I drag formulas with other cell references
example cell A1 contains formula "=stdev(a2:a7) cell a2 contains formula "=stdev(a8:a13) Is it possible to drag the formula down so that cell a3 contains "=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5, a6, a7 etc. etc. Hi Bram, See http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr B1: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) B2: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) It was easier to test and verify using SUM instead of stdev, but the formula is same NOTE the formulas are in a different colu...

Cell Selection?
I have a colum of numbers 198 0 0 198 2 What I need, is to be able to select 3. the 198's are not going to be used in the next part of my equation. That seems simple enough, however all the numbers could be usable (not 198) and I need to use just the first three. Any ideas? One interp / way, using non-array formulas Assuming source numbers in A1 down Put In B1: =IF(COUNT($C$1:C1)>3,"",C1) In C1: =IF(ISERROR(SMALL(D:D,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0))) In D1: =IF(A1="","",IF(A1=198,"",ROW())) Select B1:D...

Formulas don't work in certain cells #2
nope, the cells are formatted as numbers. I simply cannot figure thi out. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View this thread: http://www.excelforum.com/showthread.php?threadid=27423 Being formatted as numbers doesn't automatically mean they are numbers. Copy an empty cell formatted to General. Select your range of numbers and Paste Special>Add>OK>Esc Gord Dibben Excel MVP On Mon, 1 Nov 2004 11:59:46 -0600, kalik247 <kalik2...

want to add all $ in column c where column A is the same
I'm very new at Excel, and a real math dummy. I've figured out how to enter a formula when all the $ I want to add are together, but I can't figure out how to do that when I want to select only the $ values for certain items listed throughout the spreadsheet. For example: I keep a running list of Architects, their projects and $ values of each project as they are assigned. I want to automatically calculate the total current $ value for each Architect without having to sort them in order, or create a separate table for each architect. Can I do that? Here's what th...

Adding attachments to email merges
I am trying to create an email merge in Outlook, with a PDF attachment. It all works ok, and the message sends but the attaachment is always stripped off the message - I have tried different files and the attachement never comes through, or even gets mentioned in the received message. What am I doing wrong? Any suggestions gratefully received. Many thanks m.nutt1 Not supported. You'd need to use third party software for that. http://www.slipstick.com/addins/mail.htm#massmail -- Russ Valentine [MVP-Outlook] "m.nutt1" <mark@emqc.co.uk> wrote in message news:%23qnq...

Remove last letter from column
Hi, I have a list of titles and some titles have a letter A or B at the end.. is there a function/formula I can use to remove them if it ends in A or B? For example (my list): Accounting Sr Mgr B Accounts Payable Sr Mgr B Ambulatory Plng Sr Prog Dir A Need it to look like this: Accounting Sr Mgr Accounts Payable Sr Mgr Ambulatory Plng Sr Prog Dir Thanks! This will get rid of the A or B at the end along with the space before it. Assuming the value is in A5: =IF(OR(RIGHT(A5,2)=" B",RIGHT(A5,2)=" A"),LEFT(A5,LEN(A5)-2),A5) kvc wrote: > Hi, I have a list of titles a...

reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4 players, then all 4 has to play one another. if I have the names John Mike Sally Bill Then I'd like to type them into a columns and write a formula in a row to pick up the names the spreadsheet should then look like this: John Mike Sally Bill John Mike Sally Bill I think it may be achieved with the Indirect() function, but my Excel 2007 help seems broken and I can't figure it out without an example. Thanks. MikeB With names in A2:A5 Enter in B1 =INDIRECT("A"&COLUMN(B1)) Or...

sum of a column according to two or more variables
I have a master log with a column called hours lost, a column calle vendor, one called problem type and the rows are labeled and sorted b date. I would like to sum the hours lost column for each month according t the month and vendor, and have the sum end up in one cell I would also like to sum the hours lost column for each month accordin to the month and problem type and have the sum end up in one cell basically I only want the hours lost data for a specific vendor an month at one time or a specific problem type and month at one time, bu I don't know how to set up the formula correctl...

Workflow rule on (Order)Products and columns of related entities in advanced find view
Hi, Does anyone know whether it's possible to create workflow rules on (Order)Products, since the entity Products isn't part of the standard workflow entity? In my example I have added a new (expiry) date attribute on the OrderProduct form. Now I would like to add a workflow rule on that datefield to create a task when the expiry date is nearly reached; but the problem I have is that i can't "reach" the fields on the OrderProduct form to put a workflow rule on? Another problem I have is that I've created an advanced find query in which I query customers who have or...

Page Number in Repeat Rows area
Is there a way that I can put a Page Number in the Repeat Rows area of a sheet - and get it to update when I print? I have found one piece of VBA that puts a page number within a cell but it is only updated if the cell is outside the Repeat Row area. When it is within the Repeat Rows area I just get a page number of 1 corresponding to the original row location.. I do some VBA programming but am not an expert. TIA cheers Chris Nothing comes to mind that doesn't require VBA code. You can paste the following in the sheet header (File | Page Setup) and page x of y will print on ea...

Access, average several fields in one row
I have several rows of data in a field, I need to average all the entries in one row I have 12 fields for 12 months of data, I need the average of the sum of all non blank entries. For example 3 months completed, the solution in Excel is (field1+field2+field3)/3 I am looking for method to average the sum in Access One way if you can't change your data is to use a VBA function. I've posted one below. You would call it in a calculated field in a query. Assuming your field names are the abbreviated month names the expression might look like the following. Field: fRow...

getting added to other peoples meetings in shared calendar
I opened shared calendars in my group by choosing their name and then the email messages went out giving them access to my calendar and requesting to view theirs. Ever since, I have been copied in as a "required attendee" on all meetings for everyone. I get the calendar items on my calendar and emails requesting Accept or Decline in my inbox. When others view view thru their outlook, they do not see me as a required attendee? What am I doing wrong? are you adding them as delegates or just giving them permission to view the calendar (by right clicking on the calendar fold...

Count occurrances with conditions
I am trying to count in 3 columns. Column A will have entries of "B or "S" and columns B and C will have "1" or "0". A B C B 0 1 B 1 0 B 1 1 S 1 1 I want to count how many times column C has a 1 with a B or S. That i no problem, I can do that. I also want to count the number of time column B has a 1 with a B or S. That is no problem, I can do that. However, the third count I want to count is when a 1 appears in bot column B and...

Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max necessary by hovering the cursor between the 2 rows I want to adjust and double-clicking, but sometimes this doesn't always work. Why is that that - do I need to adjust a setting? And is there any way to set it so that if text is added or deleted the row height would change automatically so thatthe text fit appropriately? Set the row format to Autofit and cells to Wrap Text Gord Dibben MS Excel MVP On Tue, 19 May 2009 12:14:04 -0700, DaveL <DaveL@discussions.microsoft.com> wrote: >I know I should be ...

Adding Members- Dist. Lists
Not sure if I should be posting this here or in the SBS group. Does anyone know if there is a way to assign a permission to a user in our office to be able to update membership lists for distribution groups while she is in Outlook? Currently, when she clicks the "To:" button in a new email, right-clicks the distribution group, selects properties, goes to "modify members", it lets her select the new member but then when she clicks ok she will get an error mssg saying she doesn't have permission to do this. thx! on the security tab of the DL, does she have the &qu...