SUMIF Formula with exact text match, ignoring numbers

**catsamjewelry** · 01-28-2023, 04:01 PM

Hi guys!

I need your help! I'm using the SUMIF formula in my inventory to calculate quantities that are referred to with a code. The information is located on another sheet. The code typically consists of 2-5 letters and 1-3 numbers so for example BESSR213. Now I've been using: =SUMIF(Sheet6!E5:E500,"*BES*",Sheet6!I5:I500) but that way all codes including "BES", e.g BESS, BESSH, and so on are included in the result.
I've been investigating here and on other forums to find solutions and actually found someone here with the exact same problem but the solution =SUMPRODUCT(SUMIF(Sheet6!$E5:$E500,CHOOSE({1,2,3},$A5&" *","* "&$A5,"* "&$A5&" *"),Sheet6!I5:I500)) does not work for me. The result is always 0. I've been trying to modify the formula but without success.

Thanks in advance!

**tanasedn** · 01-28-2023, 04:20 PM

Hi try to attach a worksheet with some relevant information and some espected results
See the yellow banner at the top of this page
"Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen."

**catsamjewelry** · 01-28-2023, 04:42 PM

Sorry, here the example.

**tanasedn** · 01-28-2023, 05:28 PM

All codes after letters begin with 0?
In this case:

Formula:

Please Login or Register  to view this content.

**wk9128** · 01-28-2023, 08:50 PM

pls try this formula
worksheet or Tab name : Sheet2
Cell C4 formula , Drag down

Formula:

Please Login or Register  to view this content.

**catsamjewelry** · 01-29-2023, 04:49 AM

I could modify all the code to make all codes to begin with 0 after the letters but ideally, that should not matter.
Thanks in advance!

**wk9128** · 01-29-2023, 04:52 AM

Hi @catsamjewelry You're Welcome. Glad to help . Thank You for the feedback.

If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post,you will see the word PREFIX on the upper left, press the button to select [SOLVED] select "Mark your thread as Solved".
- Please click on the *Add Reputation button at the bottom left. Whoever helps you, give it to whoever you like

**Glenn Kennedy** · 01-29-2023, 05:19 AM

Hi.

Is your forum profile showing the Excel PRODUCT that you need this request to work with? I have no idea what 15.13.3 means.

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

**catsamjewelry** · 01-29-2023, 05:28 AM

Just modified it. I'm working with excel 2015 for mac. In the "about" is displayed: version 15.13.3 (150815).
I would be really thankful for a solution that does not require the first number of the code to be a 0.
Thanks for your effort in advance!

**Glenn Kennedy** · 01-29-2023, 05:53 AM

You'll almost certainly need to use a helper column,as your Excel version is rather dated. I put it in sheet 1, but it can even be on another (hidden) sheet.

The helper is:
=IFERROR(LEFT(B4,MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},$B4)),"",FIND({1;2;3;4;5;6;7;8;9;0},$B4)))-1)&" ","")

Then the formula is:

=SUMIF(Sheet1!H:H,Sheet2!B4&" ",Sheet1!F:F)

see file.

**Phuocam** · 01-29-2023, 06:41 AM

Also, you can use this formula for the helper column:

=LEFT(B4,AGGREGATE(15,6,FIND({1;2;3;4;5;6;7;8;9;0},$B4&0),1)-1)&" "

**catsamjewelry** · 01-31-2023, 05:22 PM

Thanks for your time and effort!
This actually worked out, I'm still considering getting newer hardware to run a newer version of excel since its quite a detour.
But you made it possible with what I have. Really appreciate it!

SUMIF Formula with exact text match, ignoring numbers

LinkBack

Thread Tools

Rate This Thread

Display

SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Re: SUMIF Formula with exact text match, ignoring numbers

Thread Information

Users Browsing this Thread

Similar Threads

How to match cells that have numbers & text and aren't exact matches

SUMIF Formula with exact match text

[SOLVED] sumif which finds text, not an exact match, but a close match instead

[SOLVED] Exact text match within a SUMIF function

[SOLVED] Formula to add numbers but ignoring text

[SOLVED] Find Exact Match Text String Using Formula

If/Match Formula, matching against contained numbers, but not exact?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions