IF Statement Question 0 values & blank cells
 

Here is my formula:

IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","",
SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5)))

I get a 0 value for a blank cell.. I would like nothing returned for a
blank cell.

If I use =0, "", I will not receive a value for cells that have a 0 in them.

Help!
Thanks,
Josh

One possible way

=IF(COUNTBLANK(INDEX(A3:E6,0,MATCH(B7,A2:E2,0)))=4 ,"",SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*( $B$3:$E$5)))



--
Regards,

Peo Sjoblom

(No private emails please)


"joshmd9909" wrote in message
...
Here is my formula:

IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","",
SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5)))

I get a 0 value for a blank cell.. I would like nothing returned for a
blank cell.

If I use =0, "", I will not receive a value for cells that have a 0 in
them.

Help!
Thanks,
Josh

I tried the formula below and I am receiving a 0 value when the data cell is
blank.



"Peo Sjoblom" wrote:

One possible way

=IF(COUNTBLANK(INDEX(A3:E6,0,MATCH(B7,A2:E2,0)))=4 ,"",SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*( $B$3:$E$5)))



--
Regards,

Peo Sjoblom

(No private emails please)


"joshmd9909" wrote in message
...
Here is my formula:

IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","",
SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5)))

I get a 0 value for a blank cell.. I would like nothing returned for a
blank cell.

If I use =0, "", I will not receive a value for cells that have a 0 in
them.

Help!
Thanks,
Josh

It worked for me when I tested a small sample, however I don't know what
your data cell is, maybe if you could describe how the layout is and what's
in the cells?

--
Regards,

Peo Sjoblom

(No private emails please)


"joshmd9909" wrote in message
...
I tried the formula below and I am receiving a 0 value when the data cell
is
blank.



"Peo Sjoblom" wrote:

One possible way

=IF(COUNTBLANK(INDEX(A3:E6,0,MATCH(B7,A2:E2,0)))=4 ,"",SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*( $B$3:$E$5)))



--
Regards,

Peo Sjoblom

(No private emails please)


"joshmd9909" wrote in message
...
Here is my formula:

IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","",
SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5)))

I get a 0 value for a blank cell.. I would like nothing returned for a
blank cell.

If I use =0, "", I will not receive a value for cells that have a 0 in
them.

Help!
Thanks,
Josh

Here is how my data is set up.

Column A is a set of metric ID's. Row 2 has the dates in them. B3-E5 in
this example is where the data is.

Example
10/01/01 10/08/05
1354 -1.5 0
1469 25 "blank cell"
1568 33,000 40,000

Then on a different spreadsheet I have the metric ID #'s listed in column A
& a single date that is in cell B7. I'm comparing metric ID's & the date and
then pulling the data where that criteria is matched. Sometimes I will have
a blank cell though, and I need a blank returned, not a 0!! However as you
can see some of the data will have a 0 value & I also need that returned.

Thanks for your help in advance!


IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","",
SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5)))
"Peo Sjoblom" wrote:

It worked for me when I tested a small sample, however I don't know what
your data cell is, maybe if you could describe how the layout is and what's
in the cells?

--
Regards,

Peo Sjoblom

(No private emails please)


"joshmd9909" wrote in message
...
I tried the formula below and I am receiving a 0 value when the data cell
is
blank.



"Peo Sjoblom" wrote:

One possible way

=IF(COUNTBLANK(INDEX(A3:E6,0,MATCH(B7,A2:E2,0)))=4 ,"",SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*( $B$3:$E$5)))



--
Regards,

Peo Sjoblom

(No private emails please)


"joshmd9909" wrote in message
...
Here is my formula:

IF(SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($ B$3:$E$5))="","",
SUMPRODUCT(($A$3:$A$5=$A$8)*($B$2:$E$2=$B$7)*($B$3 :$E$5)))

I get a 0 value for a blank cell.. I would like nothing returned for a
blank cell.

If I use =0, "", I will not receive a value for cells that have a 0 in
them.

Help!
Thanks,
Josh

Just tinkering around ..

Assuming this is in B3:E5
10/01/01 10/08/05
1354 -1.5 0
1469 25 "blank cell"
1568 33,000 40,000

and if we have:
in A8: 1469
in B7: 10/08/05

Putting in say, B8:

=IF(ISBLANK(INDIRECT(
ADDRESS(MATCH(A8,A:A,0),MATCH(B7,2:2,0)))),"",
INDIRECT(ADDRESS(MATCH(A8,A:A,0),MATCH(B7,2:2,0))) )

seems to return the desired result, i.e. blank

Changing the input in A8 to say: 1354
would return in B8: 0, and so on ..

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Correction to description of set-up in earlier response, sorry ..

Line:
Assuming this is in B3:E5

should read as:
Assuming this is in A2:C5

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--