sumproduct help

[Guest]

I have data on two worksheets and I want to sum data on one sheet if the data
on the second sheet matches. The data in sheet one is either 4, 7, or 10
digits (and is in text format, xxxx-xx-xx) but sheet 2 is only 4 digits. I
have the formula below but it returns zero and that isnt right.

What do I need to do to fix this?

=SUMPRODUCT(--((LEFT(TRIM('sheet 1'!A2:A3000),4))='sheet 2'!B349),'sheet
1!'B2:B3000)

Thanks

 

[Guest]

Assuming the reference sheets are: Sheet1, Sheet2 (no spaces)
try this revised version
=SUMPRODUCT(--((LEFT(TRIM(Sheet1!A2:A3000),4))=Sheet2!B349&""),Sheet1!B2:B3000)

LEFT's result is a text number, while Sheet2's B349 probably contains a real
number. The above makes Sheet2's B349 number into a text number (via:
Sheet2!B349&""), for correct matching.

If your sheets are named: Sheet 1, Sheet 2 (with a space),
try this:
=SUMPRODUCT(--((LEFT(TRIM('Sheet 1'!A2:A3000),4))='Sheet 2'!B349&""),'Sheet
1'!B2:B3000)

 

[Guest]

If there's leading zeros in the results returned by LEFT,
then try instead:

Reference sheets are: Sheet1, Sheet2 (no spaces
=SUMPRODUCT(--((LEFT(TRIM(Sheet1!A2:A30),4))=TEXT(Sheet2!B349,"0000")),Sheet1!B2:B30)

Reference sheets are: Sheet 1, Sheet 2 (with a space)
=SUMPRODUCT(--((LEFT(TRIM('Sheet 1'!A2:A30),4))=TEXT('Sheet
2'!B349,"0000")),'Sheet 1'!B2:B30)


---