SUMPRODUCT problem

[Alby]

Hi all
I have a workbook that I am using the sumproduct function:
Spreadsheet: Totals
=SUMPRODUCT((Analysis!$B$4:$B$155=Totals!A3)*(Analysis!
$F$4:$F$155))

This gathers the data from the sheet "analysis". The
cells it's looking up are manually entered.

However when I look up some cells that have a formula in :
=IF(ISNA(VLOOKUP(B55,'Driver Costs'!
$A$2:$B$6,2,FALSE)),"",VLOOKUP(B55,'Driver Costs'!
$A$2:$B$6,2,FALSE)*C55)
using

=SUMPRODUCT((Analysis!$B$4:$B$155=Totals!A3)*(Analysis!
$I$4:I155))
I get a "VALUE" result.

How do I get around that?

Thamks
Alby

 

[Peo Sjoblom]

It's because the vlookup formula returns a text string "", try this
adaptation

=SUMPRODUCT(--(Analysis!$B$4:$B$155=Totals!A3),Analysis!$I$4:I155)

 

[Alby]

Thanks Peo, works perfectly

Alby

-----Original Message-----
It's because the vlookup formula returns a text string "", try this
adaptation

=SUMPRODUCT(--(Analysis!$B$4:$B$155=Totals!A3),Analysis! $I$4:I155)

--

Regards,

Peo Sjoblom




.