SUMPRODUCT formula help

[Guest]

The formula below was working when D188 was D14 because I had entries from D9
to D14, but when I copied down to row 188 where there is no data yet, it
created a "#N/A" for result. I need to have those cell without data to be
included in the formula.
Can someone help with this problem?
Many thanks in advance.

Serge

=IF(OR(A32="",B32=""),"",SUMPRODUCT(('Bolt List'!$D$9:$D$188=A32)*('Bolt
List'!$J$9:$J$188=B32)*('Bolt List'!$E$9:$E$188="SAE Gr.5 Min. Cad.
Plated"),'Bolt List'!$C$9:$C$188))

 

[Don Guillett]

why not change your formula so there is no na by using the same if idea
shown.

 

[Guest]

Hello Don,
Could you show me an example please!
Serge

 

[Bob Phillips]

The formula is fine. You must have a #N/A in the data between 15 and 188.
Find it and correct it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hello Bob,
Yes I have in the "Bolt List" sheet for all the rows that do not have
entries yet with this formula:
=VLOOKUP(I40, INDIRECT(VLOOKUP(D40, $R$1:$S$9,2)),2,TRUE)
Now maybe the VLOOKUP formula needs modification?
Can the this formula give me an empty cell if there is no entries in the
corresponding cells?
Thanks a million
Serge

 

[Bob Phillips]

Yes, use

=IF(ISNA(VLOOKUP(I40, INDIRECT(VLOOKUP(D40,
$R$1:$S$9,2)),2,TRUE)),"",VLOOKUP(I40, INDIRECT(VLOOKUP(D40,
$R$1:$S$9,2)),2,TRUE)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

I have learned a lot.
Thank so much Bob and to everyone else that had an input in my request.

 

[Bob Phillips]

Glad we could help Serge.

Regards

Bob

I have learned a lot.
Thank so much Bob and to everyone else that had an input in my request.