#VALUE! error

[Somersetinventory]

I have been trying to add up (from E4 to AV4) them. However there are blank cells which I tried- TO GO special and select blank- Type 0 and Click Ctrl+Enter. But it changes only Rows and columns that are entirely blank. I know there is a simpler method. Would be great if you assist.  

@Riny_van_Eekelen 

[Somersetinventory]

@Riny_van_Eekelen 

[#3 · Accepted solution]

@Somersetinventory Just use 

=SUM(E4:AV4)

and copy it down.

[#4]

@Somersetinventory 

Something you might like to consider is the use of Excel Tables which treat your table as a single object for the purposes of formatting or calculation.  The row sum looks a bit more complicated but it will propagate to any new rows you might add without user intervention,

= SUM(Consumption[@[AR-216]:[Sample-11]])

[@ means the current row and [AR-216] is your first column]

Similarly the column totals move to accommodate new rows and the formula is selected from a simple dropdown.

=SUBTOTAL(109,[AR-249])

[#5]

Quiet a descriptive explanation, will be trying it for sure in the next report I prepare. Thank you so much.

[#6]

Thank you again.

[#7]

@Somersetinventory 

@Peter Bartholomew 

I am trying your method of using tables and totaling SUM. Could you please assist me steps. As I am finding it difficult with using tables and while totaling rows, after =SUM( CONSUMPTION[AR-263]:[AR-266]]) 

Do I have to type what I have made BOLD.

Thanx in advance.

[#8]

@Somersetinventory 

You don't have structured table in the file, that's just a range. To convert it to table select the range, Ctrl+T, rename the table as desired, now you may use formulas with structured references. Please check attached.

[#9]

Thank you @Sergei Baklan. I understood, I was just selecting the range for the table and not creating a table. Thanx Again

[#10]

@Somersetinventory , you are welcome