excel 2007 function required 1

[Rebellion34]

Hi I am trying to add the value of 2 cells say D1,E1 and display the result in A1 but need it to only add these values if the time is less that 13:00 in cell B1 and has a specific word in cell F1 say "BLUE"is there a function entry that will allow this or a VB code?
e.g.

A1 B1 C1 D1 E1 F1
# 13:20 2 4 BLUE (dont SUM)
# 12:00 3 3 GREEN (dont SUM)
# 12:30 1 5 BLUE (SUM)
# 11:15 6 6 RED (dont SUM)
any help would be appreciated
Thanks

 

[SkipVought]

Hi,

This is a simple IF function...
[tt]
=IF(CellRef="Blue",SumTheCells,0)
[/tt]


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

 

[eritguy]

Are you wanting to add columns C and D instead of D and E like you stated?
I put a simple formula that does exactly what you stated, but I added a new column with the time 13:00 for the formula to reference.
=IF(E1="BLUE",IF(B1<F1,CONCATENATE(D1," ",E1),"Not Valid"),"Not Valid")
I used concatenate since you can't add words and numbers together, but if you intend to add C and D, then just use the sum instead of concatenate.
Hope this helps.

 

[SkipVought]

Sorry, I failed to see the time requirement...
[tt]
=IF(and(ColorCellRef="Blue",TimeCellRef<TIME(13,0,0),SumTheCells,0)
[/tt]

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

 

[Rebellion34]

Thanks guys these responses were very valuable and assisted me in getting the required formula to work. so hats off to you for all the help.

thanks again

 

[PaulBricker]

Skip, the formula is missing a closing ")". I have it as
=IF(and(ColorCellRef="Blue",TimeCellRef<TIME(13,0,0)[blue])[/blue],SumTheCells,0)

Paul