Error- Value

[Eric Tsang]

Dear all

I have to handle a huge excel file and tabulate for their "Total"

See attached.

I have no idea why it prompted "Value" error on the Total column, i tried to copy the column to the Ërror row but still not working

Can anyone help.

Also is there any better way to do the calculation in the file?

Thanks for enlightened me

[sweep]

Hi,

Some of your cells in the range C4:AB5 contain a space which is handled as text and therefore generates the error.

Highlight all the cells in that range, and tap delete.

[JohnTopley]

The VALUE error was "unknown" data (space) in one cell ("Clear contents" removed the error)


I would use a table with your ROW 2 headings in one column and corresponding value in other.

I would then select each option in (for example row 4) with an "X" then use SUMPRODUCT to calculate the cost.

=SUMPRODUCT(($A4:$AA4="X")*(AT2:AT30))

where AT2:AT30 are values associated with each header

"X" could a number if a cost is related to number of people with default value of 1

then use ....

=SUMPRODUCT(($A4:$AA4)*(AT2:AT30))