sumproduct help
 

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

sumproduct help
 

Assuming the reference sheets a Sheet1, Sheet2 (no spaces)
try this revised version:
=SUMPRODUCT(--((LEFT(TRIM(Sheet1!A2:A3000),4))=Sheet2!B349&""),S heet1!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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Todd" wrote:
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

sumproduct help
 

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

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

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---