Multiply two series and add results

  • Thread starter Thread starter terrapinie
  • Start date Start date
T

terrapinie

I have a large workbook with many sheets for each charge number on a
project.
I had to copy and paste a sheet into the workbook and am trying to
remove the links to the original workbook. I have, in addition to the
sheet for each charge number, a sheet with pay rates for each person.
I need to multiply two columns of information together: the hours for
each person * the pay rate for each person. The formula that was in
the sheet (and on every single other sheet in the chart) is this:
=SUM(C3:C24*'Costs'!$C3:$C24) (With 'Costs' being the sheet with
payrates on it.)
This formula produces valid answers for everysingle sheet in the
workbook except for the one that was copied and pasted. I have tried
several variations to this formula, with quotes and without, with
dollar signs and without, extra parentheses, etc. Nothing seems to
work. I have also tried copying the formula from another sheet in the
workbook (where it does work) and I still get #VALUE for my answer.
It says "A value used in the formula is of the wrong data type".
However, they are numbers. They are formatted as 'accounting' with no
decimal. (The same format as all the other pages.)
I can't seem to figure out why this formula comes up invalid.
Also, in a group of rows just above this error, I have a similar row,
multiplied by the price of each individual person. And this formula
is identical to the one I'm using just below, but refering to another
series on the costs sheet:
=SUM(C3:C24*Costs!$C29:$C50)
I have noticed that some of the formulas, when I click on the cell
looks like this in the formula bar:
{=SUM(C3:C24*Costs!$C3:$C24)}
However, as soon as I click in the formula bar to edit, the outside
{brackets} disappear. I have tried manually placing them in the
formula for this problem row - and it just makes the formula appear as
text entered.
How do I get rid of this error?? Please help!!!

Thanks,
Laurie
 
{=SUM(C3:C24*Costs!$C3:$C24)}

The above is an array formula

The curly braces { } are inserted by Excel
after the formula is array-entered
(you don't type-in these curly braces .. )

Array entering a formula means
pressing CTRL + SHIFT + ENTER
(instead of just pressing ENTER)

Try this

Put in a cell: =SUM(C3:C24*Costs!$C3:$C24)

Hold down CTRL and SHIFT keys, and press ENTER

The formula will appear as: {=SUM(C3:C24*Costs!$C3:$C24)}

And it should work ok now ..
 
Thanks a ton!!! I knew it had to be something simple - but I've been
staring at this same workbook for so long I see it in my sleep!!!

Thanks again for the help-
Laurie
 
Back
Top