# Thread: Formula Help - Re-Post (Excel 97 SR2)

1. ## Formula Help - Re-Post (Excel 97 SR2)

Here is my re-post with attachment. I see the attachment file name in the "attach a file" window, so if it isn't attached this time, I must be doing something wrong and would appreciate advice.

Attached is an example of a sum problem in a worksheet. Except of course there are dozens of rows and columns in the actual worksheet. How can I write a formula as shown, and be able to add and delete rows and still keep the formula valid? Thanks.

2. ## Re: Formula Help - Re-Post (Excel 97 SR2)

You can do this using a array formula, multiplying two matrices. e.g. The first matrix is in A1:A4, but as this is a 4 x 1 matrix, you have to transpose is to a 1 x 4 matrix before you can multiply it with the 4 x 1 matrix in C1:C4. The result will be a 1 x 1 matrix or a simple scalar. Like this

={MMULT(TRANSPOSE(A1:A4);C1:C4)}

which you have to enter as a array formula (that's indicated by the {}).
I hope I guessed the English formula expressions rightly for matrix multiplication and transposing of a matrix. Change the semicolon into a comma if your regional settings are English.

3. ## Re: Formula Help - Re-Post (Excel 97 SR2)

It works! Thanks very much for your help.

4. ## Re: Formula Help - Re-Post (Excel 97 SR2)

Your attachment worked fine this time.

Two things:
1. Attachments mostly get lost when you preview after attaching.
2. You can edit a post and attach a file after you've posted.

5. ## Re: Formula Help - Re-Post (Excel 97 SR2)

Actually, there is another way to do it. I must have thought of it in the first place. That method will also solve problems with empty cells (they will be treated as zero), which cause the #VALUE problem with the previous solution. Therefore, you should use the SUMPRODUCT worksheetfunction (Again, I hope I use the right English for this buitl-in function). This function needs 2 or 3 matrices as arguments.

6. ## Re: Formula Help - Re-Post (Excel 97 SR2)

What I believe Hans is suggesting is filling across with this formula:

=SUMPRODUCT(\$A\$1:\$A\$4,C1:C4)

7. ## Re: Formula Help - Re-Post (Excel 97 SR2)

The SumProduct function solves my problem! Thanks to everyone for their helpful comments.

8. ## Re: Formula Help - Re-Post (Excel 97 SR2)

Hi Hans,

<<I hope I use the right English for...>>

Have you seen my xlMenufundict.zip at http://www.bmsltd.ie/mvp? it contains translations of (most) worksheetfunctions.

Edited Mar 13th 2004 to update link

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•