using sumproduct and substring text comparion

[jdcarroll15]

I have been searching for a way to utilize sumproduct with a substring comparison of a text field. That in of itself is easy enough. What I need is to find a substring value that appears at the end of the text field rather than just finding the substring value in the text field. As an example...

A	B
dir1/file1	12
dir2/fileB	250
dir2/file12	44
dir3/file1	8

<TBODY>
</TBODY>

In this example I want to accumulate all of the values in column B where the filenames - not the directory names - exactly match. From what I think I already know, search, find and match will not work since they will match my search string in the text field no matter where it's at. That would be a problem when I'm trying to match file1, i don't want the entry for file12 to be picked up in my total for file1. The end result would look like...

A	B
file1	20
file12	44
fileB	250

<TBODY>
</TBODY>

I am starting to contemplate using a helper field but would rather try to get this in the sumproduct formula directly. One other option I will try is actually using search with the start position being the length of the text field - the length of my criteria. Maybe this is the route I have to go, but I'd like to hear back if anybody has any better or other suggestions.

 

[Aladin Akyurek]

Let Sheet1 house the data, Sheet2 the processing.

Sheet2, B2, enter:

=SUMIF(Sheet1!A:A,"*/"&A2,Sheet1!B:B)

 

[jdcarroll15]

This is a good idea but it is making the assumption that I hardcode the filenames on sheet2. I'd rather not do that as in my spreadsheet I have about 2000 entries to process thru. I want to make it more dynamic in nature which is why I'm using the sumproduct function.

 

[Aladin Akyurek]

This is a good idea but it is making the assumption that I hardcode the filenames on sheet2. I'd rather not do that as in my spreadsheet I have about 2000 entries to process thru. I want to make it more dynamic in nature which is why I'm using the sumproduct function.

Not sure what you are heading. If the files are not available with the figures (numbers) they are associated with, will a SumProduct formula won't do anything better than a SumIf formula.