SOLVED

#VALUE! error

%3CLINGO-SUB%20id%3D%22lingo-sub-2145325%22%20slang%3D%22en-US%22%3E%23VALUE!%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2145325%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-subject-wrapper%20lia-component-subject%20lia-component-message-view-widget-subject-with-options%22%3E%3CSPAN%3EI%20have%20been%20trying%20to%20add%20up%20(from%20E4%20to%20AV4)%20them.%20However%20there%20are%20blank%20cells%20which%20I%20tried-%20TO%20GO%20special%20and%20select%20blank-%20Type%200%20and%20Click%20Ctrl%2BEnter.%20But%20it%20changes%20only%20Rows%20and%20columns%20that%20are%20entirely%20blank.%20I%20know%20there%20is%20a%20simpler%20method.%20Would%20be%20great%20if%20you%20assist.%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2145325%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2145362%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2145362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F953184%22%20target%3D%22_blank%22%3E%40Somersetinventory%3C%2FA%3E%26nbsp%3BJust%20use%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(E4%3AAV4)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2145807%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2145807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F953184%22%20target%3D%22_blank%22%3E%40Somersetinventory%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomething%20you%20might%20like%20to%20consider%20is%20the%20use%20of%20Excel%20Tables%20which%20treat%20your%20table%20as%20a%20single%20object%20for%20the%20purposes%20of%20formatting%20or%20calculation.%26nbsp%3B%20The%20row%20sum%20looks%20a%20bit%20more%20complicated%20but%20it%20will%20propagate%20to%20any%20new%20rows%20you%20might%20add%20without%20user%20intervention%2C%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20SUM(Consumption%5B%40%5BAR-216%5D%3A%5BSample-11%5D%5D)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%5B%3CSTRONG%3E%40%20%3C%2FSTRONG%3Emeans%20the%20current%20row%20and%3CSTRONG%3E%20%5BAR-216%5D%20%3C%2FSTRONG%3Eis%20your%20first%20column%5D%3C%2FP%3E%3CP%3ESimilarly%20the%20column%20totals%20move%20to%20accommodate%20new%20rows%20and%20the%20formula%20is%20selected%20from%20a%20simple%20dropdown.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUBTOTAL(109%2C%5BAR-249%5D)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
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.  
6 Replies
Best Response confirmed by Somersetinventory (Occasional Contributor)
Solution

@Somersetinventory Just use 

=SUM(E4:AV4)

and copy it down.

 

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

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

@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.

 

@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.