Help on sumproduct returning errors

[Pierre]

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

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

 

[Bob Phillips]

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)

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

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

 

[Pierre]

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

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

Bob, checked my cell references. Works fine now.

Thanks for the validation.

Pierre