software

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?

=SUMPRODUCT(--(WBS!$A$2:$A$423=$A46),--(WBS!$B$2:$B$423=$F46),WBS!$E$2:$E$423)--

Regards,

Peo Sjoblom

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

hi!

=SUMPRODUCT(--(WBS!$A$2:$A$423=C1),--(WBS!$B$2:$B$423=$F46),--(WBS!$E$2:$E$423))

assuming that the text you comparing is in C1

-via135

Valerie Wrote:
gt; Hello, All!
gt;
gt; I'm having trouble getting this formula to work:
gt;
gt; {SUMPRODUCT(--(WBS!$A$2:$A$423=$A46)*(--(WBS!$B$2:$B$423=$F46)*WBS!$E$2:$E$423))}
gt;
gt; 1st section is comparing a text field
gt; 2nd section is comparing a number field
gt; 3rd section is asking for a result of a text field
gt;
gt; I tried separating the sections to determine which is producing the
gt; error
gt; and I don't receive an error message, although the numbers they
gt; produced
gt; didn't appear to be the correct location of the WBS sheet. I need it
gt; to
gt; compare two columns to determine the correct cell to pull since there
gt; are
gt; duplicates in both columns but with the criteria from both columns, the
gt; line
gt; is unique.
gt; I did import these from an SAP download, but have replaced formatting
gt; with
gt; correct formatting by pulling value of the text into a new column with
gt; VALUE() formula then pasting value.
gt; What am I missing?--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=537000Thanks, 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?

quot;Peo Sjoblomquot; wrote:

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

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=$A 46)*(WBS!$B$2:$B$423=$F46),0))

entered with ctrl shift amp; enter--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Valeriequot; gt; wrote in message
...
gt; Thanks, Peo. I'm now getting 0 instead of the error code, and that's
gt; still
gt; not correct. I also tried the other suggestion from via135, but still
gt; received the error code. Any other ideas?
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; =SUMPRODUCT(--(WBS!$A$2:$A$423=$A46),--(WBS!$B$2:$B$423=$F46),WBS!$E$2:$E$423)
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Valeriequot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hello, All!
gt;gt; gt;
gt;gt; gt; I'm having trouble getting this formula to work:
gt;gt; gt;
gt;gt; gt; {SUMPRODUCT(--(WBS!$A$2:$A$423=$A46)*(--(WBS!$B$2:$B$423=$F46)*WBS!$E$2:$E$423))}
gt;gt; gt;
gt;gt; gt; 1st section is comparing a text field
gt;gt; gt; 2nd section is comparing a number field
gt;gt; gt; 3rd section is asking for a result of a text field
gt;gt; gt;
gt;gt; gt; I tried separating the sections to determine which is producing the
gt;gt; gt; error
gt;gt; gt; and I don't receive an error message, although the numbers they
gt;gt; gt; produced
gt;gt; gt; didn't appear to be the correct location of the WBS sheet. I need it
gt;gt; gt; to
gt;gt; gt; compare two columns to determine the correct cell to pull since there
gt;gt; gt; are
gt;gt; gt; duplicates in both columns but with the criteria from both columns, the
gt;gt; gt; line
gt;gt; gt; is unique.
gt;gt; gt; I did import these from an SAP download, but have replaced formatting
gt;gt; gt; with
gt;gt; gt; correct formatting by pulling value of the text into a new column with
gt;gt; gt; VALUE() formula then pasting value.
gt;gt; gt; What am I missing?
gt;gt;
gt;gt;
gt;gt;
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

quot;Peo Sjoblomquot; wrote:

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