Ah, I see. Sumproduct works only for numbers as the end return. You could use
an array-entered multi-criteria index/match to do the job.
Since your range is huge, suggest you switch the book's calc mode to Manual
first
Then try this (it embodies the TRIM suggested for more robust matching),
array-entered**, ie press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX('BO Data'!$D$2:$D$65533,MATCH(1,(TRIM('BO
Data'!$E$2:$E$65533)=TRIM('SAP BW Data'!D2))*(TRIM('BO
Data'!$F$2:$F$65533)=TRIM('SAP BW Data'!B2))*(TRIM('BO
Data'!$G$2:$G$65533)=TRIM('SAP BW Data'!A2)),0))
(Copy the formula direct from the post, paste into the formula bar. Don't
re-type.)
**Visually check that the formula is wrapped by curly braces: { } after you
press CTRL+SHIFT+ENTER to array-enter the formula in the cell (look in that
cell's formula bar). If you don't see the curlies, that means you didn't
array-enter it properly. Click inside the formula bar, re-do the
CTRL+SHIFT+ENTER.
And if the above works for you (it should), press the "Yes" button below
from where you're reading this
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---