sumif cell begins with a number but also contains letters

**agni452** · 06-28-2013, 07:44 PM

In column A I have customer codes which can be all numbers or numbers and letters. For example, it can be 64223 or 523R3 or A382K. In column B I have invoice amounts. I need to be able to add up the amounts for each group of customers beginning with a certain number or letter (which can be listed multiple times with different invoice amounts). My problem is that when I want to add up all amounts for any codes starting with a number, it won't accept SUMIF(A:A,"6*,B:B) to add up all codes starting with 6, but it will accept SUMIF(A:A,"A*,B:B) for all codes starting with A. I can also have SUMIF(A:A,">0",B:B) and will add up all codes that are ALL NUMBERS starting with any number. But how do I SUMIF codes that start with a number but also contain letters?

**FDibbins** · 06-28-2013, 08:08 PM

It might make it easier to offer suggestions if members could see a sample workbook of the data you are working with (no sensitive info), and what your expected outcome would be
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Tony Valko** · 06-28-2013, 08:13 PM

Wildcards don't work on numbers.

You can use the SUMPRODUCT function.

=SUMPRODUCT(--(LEFT(A2:A10)="6"),B2:B10)

Or, if you use a cell to hold the criteria:

D2 = 6

=SUMPRODUCT(--(LEFT(A2:A10)=TRIM(D2)),B2:B10)

EDIT: You should avoid using entire columns as range references in the SUMPRODUCT function.