SOLVED

# #VALUE! error

Occasional Contributor

# #VALUE! error

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.
8 Replies
best response confirmed by Somersetinventory (Occasional Contributor)
Solution

# Re: #VALUE! error

@Somersetinventory Just use

=SUM(E4:AV4)

and copy it down.

# Re: #VALUE! error

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])

# Re: #VALUE! error

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

Thank you again.

# Re: #VALUE! error

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.

# Re: #VALUE! error

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.

# Re: #VALUE! error

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

# Re: #VALUE! error

@Somersetinventory , you are welcome