SUMPRODUCT formula returning #VALUE! error

Thanks, Peo. I'm now getting 0 instead of the error code, and that's still
not correct. I also tried the other suggestion from via135, but still
received the error code. Any other ideas?

"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(WBS!$A$2:$A$423=$A46),--(WBS!$B$2:$B$423=$F46),WBS!$E$2:$E$423)
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
> "Valerie" <Valerie@discussions.microsoft.com> wrote in message
> news:31EF8A10-AD5F-401E-A806-532401EE43E7@microsoft.com...
> > Hello, All!
> >
> > I'm having trouble getting this formula to work:
> >
> > {SUMPRODUCT(--(WBS!$A$2:$A$423=$A46)*(--(WBS!$B$2:$B$423=$F46)*WBS!$E$2:$E$423))}
> >
> > 1st section is comparing a text field
> > 2nd section is comparing a number field
> > 3rd section is asking for a result of a text field
> >
> > I tried separating the sections to determine which is producing the error
> > and I don't receive an error message, although the numbers they produced
> > didn't appear to be the correct location of the WBS sheet. I need it to
> > compare two columns to determine the correct cell to pull since there are
> > duplicates in both columns but with the criteria from both columns, the
> > line
> > is unique.
> > I did import these from an SAP download, but have replaced formatting with
> > correct formatting by pulling value of the text into a new column with
> > VALUE() formula then pasting value.
> > What am I missing?
>
>
>

If you want to return a text value you cannot use sumproduct, I erroneously
assumed that E2:E423 was numbers. sumproduct can only return a numeric
result so it can count a text column or a number column or it can sum a
number column. If you want to return one text value from E2:E423 where
A2:A423 is A46 and B2:B423 is F46 then you can use

=INDEX(WBS!$E$2:$E$423,MATCH(1,(WBS!$A$2:$A$423=$A46)*(WBS!$B$2:$B$423=$F46),0))

entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com





"Valerie" <Valerie@discussions.microsoft.com> wrote in message
news:C91C54F7-720F-4A59-8CC7-72564E11EDD6@microsoft.com...
> Thanks, Peo. I'm now getting 0 instead of the error code, and that's
> still
> not correct. I also tried the other suggestion from via135, but still
> received the error code. Any other ideas?
>
> "Peo Sjoblom" wrote:
>
>> =SUMPRODUCT(--(WBS!$A$2:$A$423=$A46),--(WBS!$B$2:$B$423=$F46),WBS!$E$2:$E$423)
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> http://nwexcelsolutions.com
>>
>>
>>
>> "Valerie" <Valerie@discussions.microsoft.com> wrote in message
>> news:31EF8A10-AD5F-401E-A806-532401EE43E7@microsoft.com...
>> > Hello, All!
>> >
>> > I'm having trouble getting this formula to work:
>> >
>> > {SUMPRODUCT(--(WBS!$A$2:$A$423=$A46)*(--(WBS!$B$2:$B$423=$F46)*WBS!$E$2:$E$423))}
>> >
>> > 1st section is comparing a text field
>> > 2nd section is comparing a number field
>> > 3rd section is asking for a result of a text field
>> >
>> > I tried separating the sections to determine which is producing the
>> > error
>> > and I don't receive an error message, although the numbers they
>> > produced
>> > didn't appear to be the correct location of the WBS sheet. I need it
>> > to
>> > compare two columns to determine the correct cell to pull since there
>> > are
>> > duplicates in both columns but with the criteria from both columns, the
>> > line
>> > is unique.
>> > I did import these from an SAP download, but have replaced formatting
>> > with
>> > correct formatting by pulling value of the text into a new column with
>> > VALUE() formula then pasting value.
>> > What am I missing?
>>
>>
>>

Thanks, Peo!! It worked! I had tried using Index/Match before but with the
AND function and got nowhere. I will be utilizing this way much more now
since I work a lot with text! Thanks so much!

Valerie

"Peo Sjoblom" wrote:

> If you want to return a text value you cannot use sumproduct, I erroneously
> assumed that E2:E423 was numbers. sumproduct can only return a numeric
> result so it can count a text column or a number column or it can sum a
> number column. If you want to return one text value from E2:E423 where
> A2:A423 is A46 and B2:B423 is F46 then you can use
>
> =INDEX(WBS!$E$2:$E$423,MATCH(1,(WBS!$A$2:$A$423=$A46)*(WBS!$B$2:$B$423=$F46),0))
>
> entered with ctrl + shift & enter
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
>
>
> "Valerie" <Valerie@discussions.microsoft.com> wrote in message
> news:C91C54F7-720F-4A59-8CC7-72564E11EDD6@microsoft.com...
> > Thanks, Peo. I'm now getting 0 instead of the error code, and that's
> > still
> > not correct. I also tried the other suggestion from via135, but still
> > received the error code. Any other ideas?
> >
> > "Peo Sjoblom" wrote:
> >
> >> =SUMPRODUCT(--(WBS!$A$2:$A$423=$A46),--(WBS!$B$2:$B$423=$F46),WBS!$E$2:$E$423)
> >>
> >>
> >> --
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >> http://nwexcelsolutions.com
> >>
> >>
> >>
> >> "Valerie" <Valerie@discussions.microsoft.com> wrote in message
> >> news:31EF8A10-AD5F-401E-A806-532401EE43E7@microsoft.com...
> >> > Hello, All!
> >> >
> >> > I'm having trouble getting this formula to work:
> >> >
> >> > {SUMPRODUCT(--(WBS!$A$2:$A$423=$A46)*(--(WBS!$B$2:$B$423=$F46)*WBS!$E$2:$E$423))}
> >> >
> >> > 1st section is comparing a text field
> >> > 2nd section is comparing a number field
> >> > 3rd section is asking for a result of a text field
> >> >
> >> > I tried separating the sections to determine which is producing the
> >> > error
> >> > and I don't receive an error message, although the numbers they
> >> > produced
> >> > didn't appear to be the correct location of the WBS sheet. I need it
> >> > to
> >> > compare two columns to determine the correct cell to pull since there
> >> > are
> >> > duplicates in both columns but with the criteria from both columns, the
> >> > line
> >> > is unique.
> >> > I did import these from an SAP download, but have replaced formatting
> >> > with
> >> > correct formatting by pulling value of the text into a new column with
> >> > VALUE() formula then pasting value.
> >> > What am I missing?
> >>
> >>
> >>
>
>
>