Thanks Pete.
I agree with you about the file names and sheet names. I am not the creator
of these files. I will discuss this with them to shorten it.

Have a nice day.

"Pete_UK" wrote:

Why do you need such long filenames/sheet names? Assuming this doesn't
exceed the number of allowed characters in a formula (!!), you can do
it this way:

=SUMPRODUCT(--('[CCDS PEP IB Passback Accrual Total Internal_V2.xls]
Export to Excel'!$A3:$A50000=$B$6),--('[CCDS PEP IB Passback Accrual
Total Internal_V2.xls]Export to Excel'!$D3:$D50000=A9),--(('[CCDS PEP
IB Passback Accrual Total Internal_V2.xls]Export to Excel'!$F3:$F50000=
$A$6)+('[CCDS PEP IB Passback Accrual Total Internal_V2.xls]Export to
Excel'!$F3:$F50000=$A$23)),--('[CCDS PEP IB Passback Accrual Total
Internal_V2.xls]Export to Excel'!$H3:$H50000))

Here the + acts as an OR.

Hope this helps.

Pete

On Feb 6, 4:51 pm, MrRJ wrote:
Hello,
When using the sumproduct formula, there is a column that I would like to
capture two different values to make my computation. Here is what I mean.
Say in column F, I want to use the value of A6 AND A23. Can this be done?

=SUMPRODUCT(--('[CCDS PEP IB Passback Accrual Total Internal_V2.xls]Export
to Excel'!$A3:$A50000=$B$6),--('[CCDS PEP IB Passback Accrual Total
Internal_V2.xls]Export to Excel'!$D3:$D50000=A9),--('[CCDS PEP IB Passback
Accrual Total Internal_V2.xls]Export to Excel'!$F3:$F50000=$A$6),--('[CCDS
PEP IB Passback Accrual Total Internal_V2.xls]Export to Excel'!$H3:$H50000))

Thanks,
MrRJ