Help on sumproduct returning errors
 

Biff or anybody. . .

The formula works well, however When using the answer below, I get a
#VALUE! OR #DIV/0! if a looked up value from
the B10:S10 range (labels 1 to 18) isn't found in its spot in the
corresponding verticle column.

Prefacing the formula with =IF(ISERROR(formula),"",etc or
=IF(ISNA),"",etc does not return a blank cell.

How might I return a blank if the value isn't found?


Thanks for any assistance.

Pierre

from

Hi!

Try this:


This data in the range A2:C5



12587-A 1 2
12587-A 3 2.8
12587-A 4 8
12587-A 6 65


A10 = 12587-A
B10:S10 = 1,2,3,4,5....18

Formula in B11 copied across:


=IF(SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)=0,"",SUMPROD*UCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5))



This assumes that there will be no duplicates. Such as:



12587-A 1 2
12587-A 1 2.8
12587-A 1 8
12587-A 6 65


Biff

Help on sumproduct returning errors
 

Works fine for me, I get blank when not found.

Biff checks the value for 0 and puts "" in if so.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pierre" wrote in message
oups.com...
Biff or anybody. . .

The formula works well, however When using the answer below, I get a
#VALUE! OR #DIV/0! if a looked up value from
the B10:S10 range (labels 1 to 18) isn't found in its spot in the
corresponding verticle column.

Prefacing the formula with =IF(ISERROR(formula),"",etc or
=IF(ISNA),"",etc does not return a blank cell.

How might I return a blank if the value isn't found?


Thanks for any assistance.

Pierre

from

Hi!

Try this:


This data in the range A2:C5



12587-A 1 2
12587-A 3 2.8
12587-A 4 8
12587-A 6 65


A10 = 12587-A
B10:S10 = 1,2,3,4,5....18

Formula in B11 copied across:


=IF(SUMPRODUCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5)=0,"",SUMPROD*
UCT(--($A$2:$A$5=$A10),--($B$2:$B$5=B10),$C$2:$C$5))



This assumes that there will be no duplicates. Such as:



12587-A 1 2
12587-A 1 2.8
12587-A 1 8
12587-A 6 65


Biff

Help on sumproduct returning errors
 

Bob Phillips wrote:
Works fine for me, I get blank when not found.

Biff checks the value for 0 and puts "" in if so.

--
HTH

Bob Phillips


Bob, checked my cell references. Works fine now.

Thanks for the validation.

Pierre