SUMPRODUCT with "*" partial string lookup

Hi

I know I can use "*" with a SUMIF function to look at a partial string.

But I need to look up data on a separate workbook which needs to be kept closed (or rather, cannot be opened by all users due to restrictions).

So instead, I need to use SUMPRODUCT so the links will work when workbook 2 is closed.

But can I use the "*" partial string method with SUMPRODUCT? I can't seem to get it to work.



I'm using this:
=SUMPRODUCT('[source]Sheet1'!$A:$A=D5&"*")*('[Source]Sheet1'!$E:$E)

I'm looking at the partial string in cell D5

If that partial string in is column A in workbook2, collect the sum of column E in workbook2






Your help would be much appreciated.

Hi,

No, you can't use wildcards in the same way in SUMPRODUCT. However, you can combine SUMPRODUCT with other worksheet functions to get the same effect. eg.

Please Login or Register  to view this content.

*edit: Note, if possible it would be good to reduce the number of rows SUMPRODUCT is calculating on, rather than referencing entire columns.

Hi Colin

I'm afraid that didn't work, not sure why.

Please could you explain this part?

LEN(D5))=D5



My new formula is:

=SUMPRODUCT((LEFT('[INV&COST.xls]costTTD'!$A$1:$A$5000,LEN(D5))=D5)*('[INV&COST.xls]costTTD'!$E$1:$E$5000))

Also tried:

=SUMPRODUCT((LEFT('[INV&COST.xls]costTTD'!$A$1:$A$5000,LEN(D5))=D5)*'[INV&COST.xls]costTTD'!$E$1:$E$5000)



but I'm still getting #VALUE!

Hi,

Please could you explain this part?

LEN(D5))=D5

The LEFT() function picks up the characters from a cell starting from the beginning. We need to tell it how many characters to pick up - so we tell it to pick up the same number of characters as are in the cell D5. Essentially this means that we're only paying attention to the start characters, not the end characters.

I've attached a simple version of this so you can see that it works.

I see. That makes perfect sense, Colin.

But this only works when the sheets are in the same workbook.

The source dats I'm looking at is in a separate workbook. Is there another function I need to add in, in order for this to work 'without' having to open the source spreadsheet?

Hi,

It works when the sheets are in different workbooks too, provided you have the relevant permissions to update links.

Does E1 contain a column header, ie. a non-numeric value?

If yes, then change to either of these:

=SUMPRODUCT((LEFT('[INV&COST.xls]Sheet2'!$A$2:$A$200,LEN(D5))=D5)*('[INV&COST.xls]Sheet2'!$E$2:$E$200))

=SUMPRODUCT(--(LEFT('[INV&COST.xls]Sheet2'!$A$1:$A$200,LEN(D5))=D5),'[INV&COST.xls]Sheet2'!$E$1:$E$200)