SumProduct Question

[MrRJ]

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

 

[Pete_UK]

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

 

[MrRJ]

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.

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

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

 

[Shane Devenshire]

Hi,

This is also a time to consider using range names, for example if you name
the range
Export to Excel'!$A3:$A50000 A and the other column ranges accordingly
You can shorten the formula to

=SUMPRODUCT(--('CCDS PEP IB Passback Accrual Total
Internal_V2.xls'!A=$B$6),--('CCDS PEP IB Passback Accrual Total
Internal_V2.xls'!D=A9),(('CCDS PEP IB Passback Accrual Total
Internal_V2.xl's'F=
$A$6)+('CCDS PEP IB Passback Accrual Total
Internal_V2.xls'!F=$A$23)),--('CCDS PEP IB Passback Accrual Total
Internal_V2.xls'!H))


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

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.

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

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

 

[Pete_UK]

You're welcome.

Pete