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

 

[DanteAmor]

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.

No problem, try this array formula.


{=MAX(SUMPRODUCT((ISNUMBER(SEARCH(Sheet2!$B$2:$B$9,$B2)))*(ISNUMBER(SEARCH(Sheet2!$C$2:$C$9,$B2)))*(Sheet2!$E$2:$E$9)), SUMPRODUCT((ISNUMBER(SEARCH(C2,Sheet2!$D$2:$D$9)))*(ISNUMBER(SEARCH(D2,Sheet2!$D$2:$D$9)))*(Sheet2!$E$2:$E$9)))}