SUMPRODUCT formula help

Serge · 04-10-2006, 04:20 PM

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 · 04-10-2006, 04:35 PM

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

--
Don Guillett
SalesAid Software
[email protected]
"Serge" <[email protected]> wrote in message
news:[email protected]...
> 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))

Serge · 04-10-2006, 04:45 PM

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

"Don Guillett" wrote:

> why not change your formula so there is no na by using the same if idea
> shown.
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Serge" <[email protected]> wrote in message
> news:[email protected]...
> > 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))
>
>
>

Bob Phillips · 04-10-2006, 04:55 PM

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)

"Serge" <[email protected]> wrote in message
news:[email protected]...
> 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))

Serge · 04-10-2006, 05:15 PM

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" wrote:

> 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)
>
> "Serge" <[email protected]> wrote in message
> news:[email protected]...
> > 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))
>
>
>

Bob Phillips · 04-10-2006, 06:00 PM

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)

"Serge" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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)
> >
> > "Serge" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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))
> >
> >
> >

Serge · 04-10-2006, 06:20 PM

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

"Bob Phillips" wrote:

> 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)
>
> "Serge" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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)
> > >
> > > "Serge" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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))
> > >
> > >
> > >
>
>
>

Bob Phillips · 04-10-2006, 06:45 PM

Glad we could help Serge.

Regards

Bob

"Serge" <[email protected]> wrote in message
news:[email protected]...
> I have learned a lot.
> Thank so much Bob and to everyone else that had an input in my request.
>
> "Bob Phillips" wrote:
>
> > 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)