Adding sums in rows and columns and colouring cells with conditions

[Manosh]

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

 

[Shane Devenshire]

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?

 

[manoshde]

Hi,

I haven't looked at your data but

1.  To sum based on the text "days home" you would use something of theform
=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












- 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

 

[Shane Devenshire]

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.

 

[manoshde]

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






- Show quoted text -

thats cool- i can get this to work!
cheers.
ps where is the Yes button?