SumProduct Question

  • Thread starter Thread starter MrRJ
  • Start date Start date
M

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
 
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
 
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 said:
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
Click to expand...
Click to expand...
 
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


MrRJ said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top