G
Gary
What's wrong with this formula?
=SUMPRODUCT(SUMIF(D110
114,{">0"},G110:G114*'ROI Questions'!B34:B38))
=SUMPRODUCT(SUMIF(D110
114,{">0"},G110:G114*'ROI Questions'!B34:B38))
S
Sheeloo
In
=SUMPRODUCT(SUMIF(D110114,{">0"},G110:G114*'ROI Questions'!B34:B38))
replace ">0"} with ">0"
Add ) after G114 and ( before 'ROI
in other words try
=SUMPRODUCT(SUMIF(D110114,">0",G110:G114)*('ROI Questions'!B34:B38))
What do you actually want to calculate?
=SUMPRODUCT(SUMIF(D110
114,{">0"},G110:G114*'ROI Questions'!B34:B38))replace ">0"} with ">0"
Add ) after G114 and ( before 'ROI
in other words try
=SUMPRODUCT(SUMIF(D110
114,">0",G110:G114)*('ROI Questions'!B34:B38))What do you actually want to calculate?
G
Gary
I'm simply trying to sum the product of multiplying two arrays on the same
spreadsheet when both columns are <>0. If I can do that, then I want to try
to multiply and sum arrays on two different spreadsheets in the some workbook.
spreadsheet when both columns are <>0. If I can do that, then I want to try
to multiply and sum arrays on two different spreadsheets in the some workbook.
S
Sheeloo
Try
=SUMPRODUCT(--(D110114>0),G110:G114,--('ROI Questions'!B34:B38),('ROI
Questions'!B34:B38))
This will multiply the corresoponding elements of Col D and ROI Col B and
then SUM them, when both are greater than zero. In fact
=SUMPRODUCT(--(D110114>0),G110:G114,('ROI Questions'!B34:B38))
will also give you the same result, since multiplying zero values won't add
up anything.
=SUMPRODUCT(--(D110
114>0),G110:G114,--('ROI Questions'!B34:B38),('ROIQuestions'!B34:B38))
This will multiply the corresoponding elements of Col D and ROI Col B and
then SUM them, when both are greater than zero. In fact
=SUMPRODUCT(--(D110
114>0),G110:G114,('ROI Questions'!B34:B38))will also give you the same result, since multiplying zero values won't add
up anything.
T
T. Valko
=SUMPRODUCT(SUMIF(D110114,{">0"},G110:G114*'ROI Questions'!B34:B38))
Maybe this:
=SUMPRODUCT(--(D110114>0),G110:G114,'ROI Questions'!B34:B38)
114,{">0"},G110:G114*'ROI Questions'!B34:B38))Maybe this:
=SUMPRODUCT(--(D110
114>0),G110:G114,'ROI Questions'!B34:B38)
T
T. Valko
=SUMPRODUCT(--(D110114>0),G110:G114,--('ROI Questions'!B34:B38),('ROI
Questions'!B34:B38))
You've repeated the last array:
....--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)
Maybe this is what they're after:
=SUMPRODUCT(--(D110114>0),G110:G114,'ROI Questions'!B34:B38)
114>0),G110:G114,--('ROI Questions'!B34:B38),('ROIQuestions'!B34:B38))
You've repeated the last array:
....--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)
Maybe this is what they're after:
=SUMPRODUCT(--(D110
114>0),G110:G114,'ROI Questions'!B34:B38)
G
Gary
Thank you! This worked like a charm:
=SUMPRODUCT(--(D110114>0),G110:G114,('ROI Questions'!B34:B38))
=SUMPRODUCT(--(D110
114>0),G110:G114,('ROI Questions'!B34:B38))
S
Sheeloo
Hello Biff,
That was intentional. I was trying to answer the way he had asked the
question...
I did point out that
Regards,
Sheeloo
That was intentional. I was trying to answer the way he had asked the
question...
I did point out that
=SUMPRODUCT(--(D110
will also give you the same result, since multiplying zero values won't
add
up anything.
Regards,
Sheeloo
G
Gary
OK, next step.
These two formulas work separately and produce my desired result:
=IF(G110>0,SUMPRODUCT(--(D110112>0),G110:G112,'ROI
Questions'!B35:B37))+(D114/E114)*G114
=IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111112>0),G111:G112,'ROI
Questions'!B36:B37))+(D114/E114)*G114
But when I combine the two IF statements as below, I get a VALUE error.
Where is my error?
=IF(G110>0,SUMPRODUCT(--(D110112>0),G110:G112,'ROI
Questions'!B35:B37))+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111112>0),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114
Also, in the two independent formulas in the first part of this question
above, before combining the two IF statements, I don't understand why the IF
statements don't need a closing paranthesis to end each formula. This is a
new one to me.
These two formulas work separately and produce my desired result:
=IF(G110>0,SUMPRODUCT(--(D110
112>0),G110:G112,'ROIQuestions'!B35:B37))+(D114/E114)*G114
=IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111
112>0),G111:G112,'ROIQuestions'!B36:B37))+(D114/E114)*G114
But when I combine the two IF statements as below, I get a VALUE error.
Where is my error?
=IF(G110>0,SUMPRODUCT(--(D110
112>0),G110:G112,'ROIQuestions'!B35:B37))+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111
112>0),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114Also, in the two independent formulas in the first part of this question
above, before combining the two IF statements, I don't understand why the IF
statements don't need a closing paranthesis to end each formula. This is a
new one to me.
G
Gary
I found my mistake. Actually my question about the closing parenthesis after
the IF statements got me thinking that my parenthesis were in the wrong
place, so the IF statements weren't evaluating the entire formula. This works:
=IF(G110>0,SUMPRODUCT(--(D110112>0),G110:G112,'ROI
Questions'!B35:B37)+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111112>0),G111:G112,'ROI Questions'!B36:B37)+(D114/E114)*G114,â€â€))
the IF statements got me thinking that my parenthesis were in the wrong
place, so the IF statements weren't evaluating the entire formula. This works:
=IF(G110>0,SUMPRODUCT(--(D110
112>0),G110:G112,'ROIQuestions'!B35:B37)+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111
112>0),G111:G112,'ROI Questions'!B36:B37)+(D114/E114)*G114,â€â€))
S
Sheeloo
Glad you worked it out...
When you type ) it briefly show the matching ( .... this is a good way to
find the pair
Another option is to click on select on part of the formula and click fx
or use Evaluate to see how the formula is evaluated
Safest way is to enter () together and then put the elements in e.g.
=IF(,,) and then put the apprpriate elements
When you type ) it briefly show the matching ( .... this is a good way to
find the pair
Another option is to click on select on part of the formula and click fx
or use Evaluate to see how the formula is evaluated
Safest way is to enter () together and then put the elements in e.g.
=IF(,,) and then put the apprpriate elements
G
Gary
I just discovered I still have a problem.
This part of my formula G111:G112,'ROI Questions'!B36:B37 is not computing
the multiplication of the two arrays. The formula adds the values in
G111:G112, but does not multiply by 'ROI Questions'!B36:B37.
This part of my formula G111:G112,'ROI Questions'!B36:B37 is not computing
the multiplication of the two arrays. The formula adds the values in
G111:G112, but does not multiply by 'ROI Questions'!B36:B37.
S
Sheeloo
Pl. paste the formula so that we can see...
G
Gary
I solved it!
In the second part of my formula
IF(G110<0,(D110/E110*G110*-1)+SUMPRODUCT(--(D111112>0),G111:G112,'ROI
Questions'!B36:B37)+(D114/E114)*G114,â€â€)
I moved D110/E110*G110*-1 and placed it after the SUMPRODUCT function as
follows:
IF(G110<0,SUMPRODUCT(--(D111112>0),G111:G112,'ROI
Questions'!B36:B37)+(D110/E110*G110*-1)+(D114/E114)*G114,â€â€)
Must the SUMPRODUCT function always be first in a formula?
In the second part of my formula
IF(G110<0,(D110/E110*G110*-1)+SUMPRODUCT(--(D111
112>0),G111:G112,'ROIQuestions'!B36:B37)+(D114/E114)*G114,â€â€)
I moved D110/E110*G110*-1 and placed it after the SUMPRODUCT function as
follows:
IF(G110<0,SUMPRODUCT(--(D111
112>0),G111:G112,'ROIQuestions'!B36:B37)+(D110/E110*G110*-1)+(D114/E114)*G114,â€â€)
Must the SUMPRODUCT function always be first in a formula?
S
Sheeloo
Order is not important...
btw I am getting the same answer with both, as I should.
btw I am getting the same answer with both, as I should.
S
Sheeloo
Download http://wikisend.com/download/523972/Sample SUMPRODUCT.xls and see...