Excel 97 Basic Question on Formula 1

[Maine]

I'm sure this is easy and very basic but I just can't figure it out. I have an Excel 97 spreadsheet. I need to calculate each of these two columns then put the sum in one field. There are about 60 rows that are included and they skip rows here and there.
For example:
B20 * E20
B21 * E21
B22 * E22

B24 * E24
B25 * E25

B29 * E29
I don't want to total Column B * total Column E, I need each row calculated first then totalled. Thanks very much for any help you can give.

 

[JVFriederick]

Try :

=IF(B20,B20*E20,0)
or
=IF(B20,B20*E20,"&quot

and copy to the full 60 records or rows

then total this formula column.

 

[SimonDavis]

I might be suggesting exactly what you don't want, but I'm not so clear about what you're trying to do . . .

Firstly I don't think it matters about the skipped rows - you can add them anyway, as Excel is normally quite happy to add zero's together in simple arithmetic.

So if you want the sum for each individual pair of numbers, place the folowing in (for example) column f:

=sum(b1*e1)

Then copy that formula down to the bottom of the data. Any cells with either null, zero or text in it should produce a zero.

At the bottom of column F you can make the total. If you have say 100 rows of numbers, place the following underneath the column - for example in cell f102:

=sum(f1:f100)

Just one point if you're multiplying the numbers, don't try to total column B or column E - the numbers will be completely arbitrary, and multiplying the total of each column will give the wrong answer (it would only work if you were adding).

 

[Maine]

I forgot to mention that I can't add another column because I have already designed the entire spreadsheet to fit exactly on the page and I have several buttons, logos etc so it would be a major undertaking to redesign everything on the sheet.

 

[JVFriederick]

Just guessing here.....
Do you need ?
=SUMPRODUCT(B20:B60,E20:E60)

This will multiply each record together for columns B and E

 

[Maine]

SUMPRODUCT is exactly what I need. Thanks so much.