# 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?

x
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)))
``````
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
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.
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.
Finance AnalystCommented:
=IFERROR(IF(K23<>0,SUM(\$K\$22:\$K\$24)*INDEX(\$AA\$11:\$AA\$19,J25,1)),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
Executive Managing MemberAuthor Commented:
Sorry, using Excel 2003 which I do not believe supports IFERROR
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.
Executive Managing MemberAuthor Commented:
JEaston, that is returning a 0 to K 25 now matter whether there is a value in J25 or not?????
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)))
``````
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
Executive Managing MemberAuthor Commented:
I caught the row number, hence my last response.  Still trying to figure out the 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)))
``````
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
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)))
``````
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.
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.
Executive Managing MemberAuthor Commented:
I got it by rewriting the formula in V to

=IF(\$K27<>"",SUM(\$V26+\$K27-\$N27),SUM(\$V26-\$N27))
Executive Managing MemberAuthor Commented:
Thanks Guys.  Going to go take more drugs for my hyperextended knee.  May be in LaLa land for awhile.
DirectorCommented: