# Multiplying the contents of a sum of cells by a number in another cell designated by a code in another cell.

For the purposes of explanation, K22 is   \$500.00
K23 is   (125.00)
K24 is    (50.00)

I want insert codes 1 to 9 in J25 which causes the sum of K22:K24 to be mulitplied by a value in AA1, AA2, AA3...AA9 with the result being in Z25.
StatementTest2.xls
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DirectorCommented:
Assuming I understand you correctly, I think you should be able to use the Indirect function, although Offset might work too.

The formula in Z25 should be:
``````=IF(K23<>0,SUM(K22:K24)*INDIRECT("AA"&(J25+10)))
``````
0
Finance AnalystCommented:
Assuming the 3 in J25 means you want the third item in list AA11:AA19

=IF(K23<>0,SUM(K22:K24)*INDEX(AA11:AA19,J25,1))

Thanks
Rob H
0
Executive Managing MemberAuthor Commented:
Rob, you formula works great.  However, since the formula will be in the cell in each line and rarely active, I need the result to be blank or a non showing 0 in order for the running balance in column V to continue to work.  It goes to \$VALUE! when the formula is in cell and unactive.
0
Executive Managing MemberAuthor Commented:
On second thought, maybe I could modify the running formula in column V to ignore the contents of that cell when the cell is dormant.
0
Finance AnalystCommented:
=IFERROR(IF(K23<>0,SUM(\$K\$22:\$K\$24)*INDEX(\$AA\$11:\$AA\$19,J25,1)),0)
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Executive Managing MemberAuthor Commented:
Everything quit working and K25 with a value in J25 shows #NAME? as does K26 with No value in J26
0
Executive Managing MemberAuthor Commented:
Sorry, using Excel 2003 which I do not believe supports IFERROR
0
DirectorCommented:
Why not add another IF statement to Rob's formula.  E.g:
``````=IF(J23="",0,IF(K23<>0,SUM(K22:K24)*INDEX(AA11:AA19,J25,1)))
``````

This checks if the J column is empty and only does the lookup if it is not.
0
Executive Managing MemberAuthor Commented:
JEaston, that is returning a 0 to K 25 now matter whether there is a value in J25 or not?????
0
DirectorCommented:
Sorry, I didn't check the row number.  It should have been J25="".  Therefore:
``````=IF(J25="",0,IF(K23<>0,SUM(K22:K24)*INDEX(AA11:AA19,J25,1)))
``````
0
Executive Managing MemberAuthor Commented:
Never mind.  Now its working but showing a "0" in K whatever when the corresponding cell in J is empty.  Can we get rid of the 0
0
Executive Managing MemberAuthor Commented:
I caught the row number, hence my last response.  Still trying to figure out the 0
0
DirectorCommented:
Of course.  Just replace the 0 after J25="",0,

So to output a blank cell, you would need:
``````=IF(J25="","",IF(K23<>0,SUM(K22:K24)*INDEX(AA11:AA19,J25,1)))
``````
0
Executive Managing MemberAuthor Commented:
The next line, with no value in J26 reads

=IF(J26="",0,IF(K24<>0,SUM(K23:K25)*INDEX(AA\$11:AA\$19,J26,0)))

now returns 0.00
0
DirectorCommented:
You've still got the 0 in the formula.  So for J26 it would be:
``````=IF(J26="","",IF(K24<>0,SUM(K23:K25)*INDEX(AA\$11:AA\$19,J26,0)))
``````
0
Executive Managing MemberAuthor Commented:
Ok, I see the change.  That works, but now my running total in V goes to #VALUE!  on that line and every line thereafter.  I am not sure how to rewrite the formula in V, which currently is =SUM(\$V25+\$K26-\$N26) to ignore the contents of K if K is not a number.
0
DirectorCommented:
This appear to be due to column K referencing column Z.  If you change the formula in K from =IF(Z25<>0,Z25,"0") to =IF(Z25<>"",Z25,"0") it should resolve the issue.  Obviously update the row numbers depending on what row has the error.
0
Executive Managing MemberAuthor Commented:
I got it by rewriting the formula in V to

=IF(\$K27<>"",SUM(\$V26+\$K27-\$N27),SUM(\$V26-\$N27))
0
Executive Managing MemberAuthor Commented:
Thanks Guys.  Going to go take more drugs for my hyperextended knee.  May be in LaLa land for awhile.
0
DirectorCommented:
0
Executive Managing MemberAuthor Commented:
In anticipation of my next question...Is there a function that PLACES the result or contents of one cell into another cell without the destination cell containing a formula?
0
Executive Managing MemberAuthor Commented:
Never mind, maybe I should open up the next question instead.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.