SUMPRODUCT formula expanded from original

[Serge]

('Bolt List'!$H$9:$H$188="1") This part was added to the formula below to
introduce one more criteria and it does not work at the moment but it did
before the extra criteria.
column "H" could be empty or it could have numerical value, the value has no
importance but the formula needs to recongnise an entry.

Can anyone help with this formula?

Thank you in advance.

Serge

=SUMPRODUCT(('Bolt List'!$D$9:$D$188=K8)*('Bolt List'!$J$9:$J$188=L8)*('Bolt
List'!$E$9:$E$188="A325 Black.")*('Bolt List'!$H$9:$H$188="1")*'Bolt
List'!$C$9:$C$188)

[Biff]

Hi!

Did you ever get your "N/A" problem solved?

Try removing the quotes:

('Bolt List'!$H$9:$H$188=1)

What does this mean: "the value has no importance but the formula needs to
recongnise an entry"

If that means you just need to test the range for cells that have *ANY*
number in them then try this instead:

(ISNUMBER('Bolt List'!$H$9:$H$188))

Biff

"Serge" <[email protected]> wrote in message
news:[email protected]...
> ('Bolt List'!$H$9:$H$188="1") This part was added to the formula below to
> introduce one more criteria and it does not work at the moment but it did
> before the extra criteria.
> column "H" could be empty or it could have numerical value, the value has
> no
> importance but the formula needs to recongnise an entry.
>
> Can anyone help with this formula?
>
> Thank you in advance.
>
> Serge
>
> =SUMPRODUCT(('Bolt List'!$D$9:$D$188=K8)*('Bolt
> List'!$J$9:$J$188=L8)*('Bolt
> List'!$E$9:$E$188="A325 Black.")*('Bolt List'!$H$9:$H$188="1")*'Bolt
> List'!$C$9:$C$188)

[Serge]

Hello Biff,
First question: answer is yes, earlier today that part was solved. thank you
for asking.
No.2; I thought I tried without quote before, I must have missed something.
Anyway I did removed the quotes and it does work, but I prefer your last
suggestion, you hit the nail rigth on the head.
Thank you so much for your knowledge.
I hope one day to aquire enough smarts to help others

"Biff" wrote:

> Hi!
>
> Did you ever get your "N/A" problem solved?
>
> Try removing the quotes:
>
> ('Bolt List'!$H$9:$H$188=1)
>
> What does this mean: "the value has no importance but the formula needs to
> recongnise an entry"
>
> If that means you just need to test the range for cells that have *ANY*
> number in them then try this instead:
>
> (ISNUMBER('Bolt List'!$H$9:$H$188))
>
> Biff
>
> "Serge" <[email protected]> wrote in message
> news:[email protected]...
> > ('Bolt List'!$H$9:$H$188="1") This part was added to the formula below to
> > introduce one more criteria and it does not work at the moment but it did
> > before the extra criteria.
> > column "H" could be empty or it could have numerical value, the value has
> > no
> > importance but the formula needs to recongnise an entry.
> >
> > Can anyone help with this formula?
> >
> > Thank you in advance.
> >
> > Serge
> >
> > =SUMPRODUCT(('Bolt List'!$D$9:$D$188=K8)*('Bolt
> > List'!$J$9:$J$188=L8)*('Bolt
> > List'!$E$9:$E$188="A325 Black.")*('Bolt List'!$H$9:$H$188="1")*'Bolt
> > List'!$C$9:$C$188)
>
>
>

[Biff]

You're welcome. Thanks for the feedback!

Biff

"Serge" <[email protected]> wrote in message
news:[email protected]...
> Hello Biff,
> First question: answer is yes, earlier today that part was solved. thank
> you
> for asking.
> No.2; I thought I tried without quote before, I must have missed
> something.
> Anyway I did removed the quotes and it does work, but I prefer your last
> suggestion, you hit the nail rigth on the head.
> Thank you so much for your knowledge.
> I hope one day to aquire enough smarts to help others
>
> "Biff" wrote:
>
>> Hi!
>>
>> Did you ever get your "N/A" problem solved?
>>
>> Try removing the quotes:
>>
>> ('Bolt List'!$H$9:$H$188=1)
>>
>> What does this mean: "the value has no importance but the formula needs
>> to
>> recongnise an entry"
>>
>> If that means you just need to test the range for cells that have *ANY*
>> number in them then try this instead:
>>
>> (ISNUMBER('Bolt List'!$H$9:$H$188))
>>
>> Biff
>>
>> "Serge" <[email protected]> wrote in message
>> news:[email protected]...
>> > ('Bolt List'!$H$9:$H$188="1") This part was added to the formula below
>> > to
>> > introduce one more criteria and it does not work at the moment but it
>> > did
>> > before the extra criteria.
>> > column "H" could be empty or it could have numerical value, the value
>> > has
>> > no
>> > importance but the formula needs to recongnise an entry.
>> >
>> > Can anyone help with this formula?
>> >
>> > Thank you in advance.
>> >
>> > Serge
>> >
>> > =SUMPRODUCT(('Bolt List'!$D$9:$D$188=K8)*('Bolt
>> > List'!$J$9:$J$188=L8)*('Bolt
>> > List'!$E$9:$E$188="A325 Black.")*('Bolt List'!$H$9:$H$188="1")*'Bolt
>> > List'!$C$9:$C$188)
>>
>>
>>