SumProduct Question

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
 
P

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
 
M

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.

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...
 
S

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


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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct 6
Translating Formulas to Functions 0
Help, Macro wont run from a particular worksheet 3
Move data from different columns into one column, but different rows. 2
Difficult Question inside! Anyone up for it? 1
Difficult Question Inside! Anyone up for it? (formatted better, I hope) 2
Fantasy Football and Excel Functions (need lotsa help) 3
Macro for button - copy and paste rows 0

Top