SUMIF based on partial text match not working

aakoni

New Member
Joined
Jun 13, 2019
Messages
5
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
 
Last edited by a moderator:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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>
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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


 
Upvote 0
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).
 
Upvote 0
SHEET 2
A
B
C
D
E
1
BJW3
023B
BJW3-023B
10
2
BJW3
023B
BJW3-023B
20
3
BJW3
023A
BJW3-023A
30
4
BJW3
23B
BJW3-23B
20
5
BJW3
23A
BJW3-02A
40
6
WEFL
2494A
WEFL-2494A
60
7
WEFL
WM2494A
WEFL-WM2494A
40
8
WEFL
2494A
WEFL-2494A
20

<tbody>
</tbody>





SHEET 1
A
B
C
D
E
1
WEFL-WM2494A
WEFL
WM2494A
2
BJW3-023B
BJW3
023B
3
BJW3-023A
BJW3
023A

<tbody>
</tbody>


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





 
Upvote 0


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>
 
Upvote 0
SHEET 2
A
BCDE
1BJW3023BBJW3-023B10
2BJW3023BBJW3-023B20
3BJW3023A
BJW3-023A30
4BJW323BBJW3-23B20
5BJW323ABJW3-02A40
6WEFL2494AWEFL-2494A
60
7WEFLWM2494AWEFL-WM2494A40
8WEFL2494AWEFL-2494A20

<tbody>
</tbody>



SHEET 1
ABCDE
1WEFL-WM2494AWEFLWM2494A
2BJW3-023BBJW3023B
3BJW3-023ABJW3023A
4
WEFL-2494A
WFL
2494A

<tbody>
</tbody>


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.







 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top