SUMIF based on partial text match not working

[aakoni]

Hi,

I'm trying to do a SUMIF over two worksheets for a number of values in a lookup table. The idea is that in cell AV39 there should be an addition of the associated combined scores on the look up sheet, Column D.

The issue comes when there are some Pt Nos that have aslight variation in the look up table as shown in the example below. So as aresult the defects column on the main spreadsheet only counts the exact matchof the Combined cells in both sheetse.g. WEF1-WM2494A gives defect number of 24 instead of 26.



IÂ’m trying to figure out a way for both of those cells to besummed in the look up sheet for WEF1-WM2494A.


I tried the asterisk “”*”” & in the formula but that didn’treturn the correct results.


I hope that makes sense.

Thanks.


Example:
Main Sheet

 

[RoryA]

Welcome to the forum.

I'm afraid you can't upload images directly to your post. Please read this thread for alternatives: https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[DanteAmor]

Do you need something like this?


<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:148.28px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >WEF1-WM2494A</td><td style="text-align:right; ">50</td><td > </td><td style="text-align:right; ">1050</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >WEF1-WM2694A</td><td style="text-align:right; ">30</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >WEF1-WM2494A</td><td style="text-align:right; ">1000</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=SUMIF(A2:A4,"*"&24&"*",B2:B4)</td></tr></table></td></tr></table>

 

[aakoni]

Hi Dante,

Thanks for getting back to me.

Almost, I put the formula in my sheet and it works but the issue is itcounts everything that has the value of the selected cell in the lookup.

Would it be possible to introduce another column or criteria so itnarrows it down even more so e.g: splitting out Column A at the dash so:

Cell A2 becomes WEF1 and Cell B2 becomes WM2494A and Cell C2 would have the 50 value.
Assume cell A3 is WEF1, Cell B3 is 2494A and C3 is 10.
Assume cell A4 is MBR1, Cell B4 is 2494A and C4 is 5.

I want to find out the sum of the instances of 2494A for WEF1 but theformula doesn’t count Cell B2 (WM2494A) because of the WM at the front but itshould be.

If that makes sense.

Thanks

 

[DanteAmor]

Hi Dante,

Thanks for getting back to me.

Almost, I put the formula in my sheet and it works but the issue is itcounts everything that has the value of the selected cell in the lookup.

Would it be possible to introduce another column or criteria so itnarrows it down even more so e.g: splitting out Column A at the dash so:

Cell A2 becomes WEF1 and Cell B2 becomes WM2494A and Cell C2 would have the 50 value.
Assume cell A3 is WEF1, Cell B3 is 2494A and C3 is 10.
Assume cell A4 is MBR1, Cell B4 is 2494A and C4 is 5.

I want to find out the sum of the instances of 2494A for WEF1 but theformula doesn’t count Cell B2 (WM2494A) because of the WM at the front but itshould be.

If that makes sense.

Thanks

Maybe like this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:148.28px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >WEF1-WM2494A</td><td style="text-align:right; ">50</td><td > </td><td style="text-align:right; ">80</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >WEF1-2494A</td><td style="text-align:right; ">30</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >MBR1-2494A</td><td style="text-align:right; ">1000</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=SUMIF(A2:A4,"*WEF1*2494A*",B2:B4)</td></tr></table></td></tr></table>

 

[aakoni]

Hi Dante,


Thanks again! That formula does work in that instance but I have loots oflines of data lol with different values so I can’t alter the formula for eachitem because that would take too long.

I tried the formulae with the cell numbers instead of actual values,i.e: =SUMIF(A2:A4,"*B2*C2*",B2:B4);where B2= WEF1 and C2=WM249A (just inserting the cells) and it returns a 0value.

Thanks

 

[DanteAmor]

Hi Dante,


Thanks again! That formula does work in that instance but I have loots oflines of data lol with different values so I can’t alter the formula for eachitem because that would take too long.

I tried the formulae with the cell numbers instead of actual values,i.e: =SUMIF(A2:A4,"*B2*C2*",B2:B4);where B2= WEF1 and C2=WM249A (just inserting the cells) and it returns a 0value.

Thanks

You could explain with several examples, an example for each instance. What data do you have and what do you expect from the result?

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[aakoni]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]SHEET 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]BJW3
[/TD]
[TD]023B
[/TD]
[TD]BJW3-023B
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]BJW3
[/TD]
[TD]023B
[/TD]
[TD]BJW3-023B
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]BJW3
[/TD]
[TD]023A
[/TD]
[TD]BJW3-023A
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]BJW3
[/TD]
[TD]23B
[/TD]
[TD]BJW3-23B
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]BJW3
[/TD]
[TD]23A
[/TD]
[TD]BJW3-02A
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]WEFL
[/TD]
[TD]2494A
[/TD]
[TD]WEFL-2494A
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]WEFL
[/TD]
[TD]WM2494A
[/TD]
[TD]WEFL-WM2494A
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]WEFL
[/TD]
[TD]2494A
[/TD]
[TD]WEFL-2494A
[/TD]
[TD]20
[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 500"]
<tbody>[TR]
[TD]

[/TD]
[TD]SHEET 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]WEFL-WM2494A
[/TD]
[TD]WEFL
[/TD]
[TD]WM2494A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]BJW3-023B
[/TD]
[TD]BJW3
[/TD]
[TD]023B
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]BJW3-023A
[/TD]
[TD]BJW3
[/TD]
[TD]023A
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hi Dante,



Thanks again. I’m new to the forum so just trying to figureout a way to include tables.


I’ve inserted some tables to give you an example but thereare 100 more rows with different cell contents that I can’t type in.


So Sheet 1 is the main sheet and Sheet 2 is the Lookup sheetwith Column E in Sheet 1 being where the results will be displayed.


As an example, looking at ROW 1 on Sheet 1, in Cell E1 I wouldlike the result to be a lookup of Cell B1 in the lookup sheet and to return a valueof 120 (instead of 40). At the moment the formula: (=SUMIF(Sheet2!D:D,A1,Defects!E:E) returns a value of 40 because it only matched the exact WM2494Ainstead of including the other values that have 2494A as well.


The idea is that there are sometimes mismatches with thedate on the two sheets i.e some numbers having letters before them and I wantall the valid values to be counted to give an accurate result.



The concatenated (combined) columns are intended to give aunique id to the cells.


I hope this makes more sense.


Thanks

 

[DanteAmor]

Hi Dante,
Thanks again. I’m new to the forum so just trying to figureout a way to include tables.
I’ve inserted some tables to give you an example but thereare 100 more rows with different cell contents that I can’t type in.
So Sheet 1 is the main sheet and Sheet 2 is the Lookup sheetwith Column E in Sheet 1 being where the results will be displayed.
As an example, looking at ROW 1 on Sheet 1, in Cell E1 I wouldlike the result to be a lookup of Cell B1 in the lookup sheet and to return a valueof 120 (instead of 40). At the moment the formula: (=SUMIF(Sheet2!D:D,A1,Defects!E:E) returns a value of 40 because it only matched the exact WM2494Ainstead of including the other values that have 2494A as well.
The idea is that there are sometimes mismatches with thedate on the two sheets i.e some numbers having letters before them and I wantall the valid values to be counted to give an accurate result.
The concatenated (combined) columns are intended to give aunique id to the cells.
I hope this makes more sense.
Thanks

Try this

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet1</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:179.64px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">NUM</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">CONCA DATA1 & DATA2</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DATA1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DATA2</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >WEFL-WM2494A</td><td >WEFL</td><td >WM2494A</td><td style="text-align:right; ">120</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >BJW3-023B</td><td >BJW3</td><td >023B</td><td style="text-align:right; ">50</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td >BJW3-023A</td><td >BJW3</td><td style="text-align:right; ">023A</td><td style="text-align:right; ">70</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=SUMPRODUCT((ISNUMBER(SEARCH(Sheet2!$B$2:$B$9,$B2)))*(ISNUMBER(SEARCH(Sheet2!$C$2:$C$9,$B2)))*(Sheet2!$E$2:$E$9))</td></tr></table></td></tr></table>

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet2</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:193.9px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">NUM</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DATA1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DATA2</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">CONCA DATA1 & DATA2</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">VALUE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >BJW3</td><td >023B</td><td >BJW3-023B</td><td style="text-align:right; ">10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >BJW3</td><td >023B</td><td >BJW3-023B</td><td style="text-align:right; ">20</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td >BJW3</td><td style="text-align:right; ">023A</td><td >BJW3-023A</td><td style="text-align:right; ">30</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td >BJW3</td><td >23B</td><td >BJW3-23B</td><td style="text-align:right; ">20</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">5</td><td >BJW3</td><td style="text-align:right; ">23A</td><td >BJW3-02A</td><td style="text-align:right; ">40</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">6</td><td >WEFL</td><td >2494A</td><td >WEFL-2494A</td><td style="text-align:right; ">60</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">7</td><td >WEFL</td><td >WM2494A</td><td >WEFL-WM2494A</td><td style="text-align:right; ">40</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">8</td><td >WEFL</td><td >2494A</td><td >WEFL-2494A</td><td style="text-align:right; ">20</td></tr></table>

 

[aakoni]

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]SHEET 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]BJW3[/TD]
[TD]023B[/TD]
[TD]BJW3-023B[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BJW3[/TD]
[TD]023B[/TD]
[TD]BJW3-023B[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BJW3[/TD]
[TD]023A
[/TD]
[TD]BJW3-023A[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BJW3[/TD]
[TD]23B[/TD]
[TD]BJW3-23B[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BJW3[/TD]
[TD]23A[/TD]
[TD]BJW3-02A[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]WEFL[/TD]
[TD]2494A[/TD]
[TD]WEFL-2494A
[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]WEFL[/TD]
[TD]WM2494A[/TD]
[TD]WEFL-WM2494A[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]WEFL[/TD]
[TD]2494A[/TD]
[TD]WEFL-2494A[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: cms_table"]
<tbody>[TR]
[TD][/TD]
[TD]SHEET 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]WEFL-WM2494A[/TD]
[TD]WEFL[/TD]
[TD]WM2494A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BJW3-023B[/TD]
[TD]BJW3[/TD]
[TD]023B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BJW3-023A[/TD]
[TD]BJW3[/TD]
[TD]023A
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]WEFL-2494A
[/TD]
[TD]WFL
[/TD]
[TD]2494A
[/TD]
[/TR]
</tbody>[/TABLE]


Hi Dante,


Thank you SO MUCH! You’re a star! That formulaworks and that’s great!


BUT lol It’s thrown up another issue, for example if we addROW 4 to Sheet 1, the result the formula gives is 80, not 120 because it’s nowdiscounting the WM2494A in the lookup.