in a cell i have "=B2+C2+E2" the result i get is #VALUE!

Use the SUM function:

=SUM(B2,C2,E2)

The SUM function ignores text which is what a formula blank ("") is.

--
Biff
Microsoft Excel MVP


"Vader" wrote in message
...
Thanks Jacob,

The formula works prefect,

But i have 1 more question now.

In a cell i need to add up 2 other cells + the result of the formula.
eg . in a cell i have "=B2+C2+E2" the result i get is #VALUE!
B2= a number
C2= a number
E2= the formula which could be either a number it has found or blank.

Do you have any suggestions?

Vader


"Jacob Skaria" wrote:

With the sample data you have provided try the below formula...Please
note
that this is an array formula. You create array formulas in the same way
that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces
at
both ends like "{=<formula}"

=INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10="OMG")*($B$2 :$B$10=1),0))

'data in sheet1
Col A Col B Col C
Code Week Imp data
OMG 1 a
AFK 2 b
OMG 2 c
AFK 3 d
OMG 4 e

The formula is a bit lengthy after adding error handling to return
blank....With your data in Sheet1; I have tried this in Sheet2 as
below..try
the below formula in cell B2.

'in sheet2
Col A Col B Col C
OMG AFK
Week1 a
Week2 c b
Week3 d
Week4 e
Week5


=IF(ISNA(INDEX(Sheet1!$C$2:$C$10,MATCH(1,(Sheet1!$ A$2:$A$10=B$1)*(Sheet1!$B$2:$B$10=--SUBSTITUTE($A2,"Week",)),0))),"",INDEX(Sheet1!$C$2 :$C$10,MATCH(1,(Sheet1!$A$2:$A$10=B$1)*(Sheet1!$B$ 2:$B$10=--SUBSTITUTE($A2,"Week",)),0)))

PS: again an array formula

If this post helps click Yes
---------------
Jacob Skaria


"Vader" wrote:

I need to look up a value that meets 2 criteria.

My data is like this

.Code.........|Week.....|....Important Data !!
OMG | 1 | XX
AFK | 2 | XX
OMG | 2 | XX
AFK | 3 | XX
OMG | 4 | XX

Where there is no data for a week or code I want a blank cell left in
the
template below.

Where there is a match I want the "Important Data" returned.

Code Code
.......OMG........|.....AFK......
Week1| XX |
Week2| XX | XX
Week3| | XX
Week4| XX |

Im all out of ideas??