hello, I wonder if anyone can help with the following, I have a colleague
who
would like to sum and multiply at the same time in a large spreadsheet. He
can do this if he types out the whole formula as =sum(a3*a5, b3*b5, etc) but
this is extremely laborious for him. If he tries to use =sum(a3*a5:z3*z5) it
converts his formula to =sum(a5*a5:z5*z5) and gives him #value as an answer.
Is there any way round this problem?

Hi Michelle

Try
=SUMPRODUCT(A3:Z3*A5:Z5)
--
Regards
Roger Govier

Michelle Embleton wrote:
hello, I wonder if anyone can help with the following, I have a colleague
who
would like to sum and multiply at the same time in a large spreadsheet. He
can do this if he types out the whole formula as =sum(a3*a5, b3*b5, etc) but
this is extremely laborious for him. If he tries to use =sum(a3*a5:z3*z5) it
converts his formula to =sum(a5*a5:z5*z5) and gives him #value as an answer.
Is there any way round this problem?

Let him try: SUMPRODUCT
Micky


"Michelle Embleton" wrote:

hello, I wonder if anyone can help with the following, I have a colleague
who
would like to sum and multiply at the same time in a large spreadsheet. He
can do this if he types out the whole formula as =sum(a3*a5, b3*b5, etc) but
this is extremely laborious for him. If he tries to use =sum(a3*a5:z3*z5) it
converts his formula to =sum(a5*a5:z5*z5) and gives him #value as an answer.
Is there any way round this problem?

....and only after(!) learning the use of SUMPRODUCT - he may use:
=SUMPRODUCT(A3:Z3,A5:Z5)
Micky


"מיכ�ל (מיקי) �בידן" wrote:

Let him try: SUMPRODUCT
Micky


"Michelle Embleton" wrote:

hello, I wonder if anyone can help with the following, I have a colleague
who
would like to sum and multiply at the same time in a large spreadsheet. He
can do this if he types out the whole formula as =sum(a3*a5, b3*b5, etc) but
this is extremely laborious for him. If he tries to use =sum(a3*a5:z3*z5) it
converts his formula to =sum(a5*a5:z5*z5) and gives him #value as an answer.
Is there any way round this problem?

There is no need for the * operator Roger, there are no conditions.

=SUMPRODUCT(A3:Z3,A5:Z5)

works just fine

--

HTH

Bob

"Roger Govier" wrote in message
...
Hi Michelle

Try
=SUMPRODUCT(A3:Z3*A5:Z5)
--
Regards
Roger Govier

Michelle Embleton wrote:
hello, I wonder if anyone can help with the following, I have a
colleague who would like to sum and multiply at the same time in a large
spreadsheet. He can do this if he types out the whole formula as
=sum(a3*a5, b3*b5, etc) but this is extremely laborious for him. If he
tries to use =sum(a3*a5:z3*z5) it converts his formula to
=sum(a5*a5:z5*z5) and gives him #value as an answer. Is there any way
round this problem?

Agreed, Bob.
Force of habit I guess!!

--
Regards
Roger Govier

Bob Phillips wrote:
There is no need for the * operator Roger, there are no conditions.

=SUMPRODUCT(A3:Z3,A5:Z5)

works just fine