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

[Beefy1]

HEllo,
I have this formula: =SUMIFS('Master Sheet'!I:I,'Master Sheet'!B:B,A2,'Master Sheet'!C:C,">="&B2,'Master Sheet'!C:C,"<="&C2) and it is referencing a column which has last name, first name and comparing it to a table that I input last name, first name. Problem is that I do not always enter it exactly the same way. For instance, the report I pull to grab all of this data puts a space before the comma and two spaces after the comma. If I change my entry to match exactly what my report has, then the sumif formula works. However, I have too many entries to go back and enter spaces to make it the same. Is there a way to alter the formula so it only needs a general match rather than an exact match. By the way "B:B" is the reference to the data that the report outputs, "A2" is the cell in the table that I input the client names for each row. it is a relative reference.

[Pete_UK]

Give us some examples of what the data in column A looks like, compared with the data in Master sheet column B - it might be possible to use a couple of SUBSTITUTES acting on A2 to convert that into the same format as in column B.

Hope this helps.

Pete

[Beefy1]

See if this will help
Bend SUMIF.xlsx

[Pete_UK]

You can use this formula in E2 instead:

=SUMIFS('Master Sheet'!L:L,'Master Sheet'!B:B,SUBSTITUTE(A2,","," , "),'Master Sheet'!F:F,">="&B2,'Master Sheet'!F:F,"<="&C2)

Note that I've used SUBSTITUTE in the middle to change the comma in A2 to a space-comma-space to match the data in your other sheet. Copy the formula down as required.

Hope this helps.

Pete