Using SUMIF with LEFT Function

Hi guys, I need your help...

I have 3 columns named LongCol, B and Amount

i want to add Amount if LEFT(LongCol,3) = B5 ie if the first 3 digits of LongCol is equal Cell B5

I have tried this but it did not work:

=SUMIF((LEFT(LongCol,3),B5,Amount)).

please help

I

Here, try this:

Formula:

Please Login or Register  to view this content.

Try

=SUMPRODUCT((LEFT(LongCol,3)=B5)*(Amount))

SUMIF does not allow use of LEFT

but try

=SUMIF(LongCol,B5 &"*",Amount)

Thanks guys. None of them worked. Let me think of a walk around while await further expert guides.

Post a file as all the above do work.

Please attach a file (not image) so that respondents (a) do not have to type in data and (b) have data with which test any formulas. Ensure the data is representative of your actual "real world" data.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the (remove confidential) data.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.