How do I sum a column where some cells contain formulae

%3CLINGO-SUB%20id%3D%22lingo-sub-2060463%22%20slang%3D%22en-US%22%3EHow%20do%20I%20sum%20a%20column%20where%20some%20cells%20contain%20formulae%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2060463%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20sheet%20with%20multiple%20columns%20containing%20data.%20In%20one%20column%2C%20Q%2C%20I%20have%20a%20formula%20that%2C%20when%20data%20is%20entered%20elsewhere%20within%20the%20document%2C%20the%20cell%20formula%20becomes%20a%20value.%20Not%20all%20cells%20are%20populated%20with%20values%2C%20so%20the%20%22unused%22%20cells%20contain%26nbsp%3B%20%22%23DIV%2F0!%22%20until%20the%20data%20is%20entered%20elsewhere.%20I%20want%20to%20be%20able%20to%20sum%20the%20column%20in%20Q155%2C%20but%20only%20include%20the%20cells%20containing%20values%20(i.e.%20excluding%20the%20%22unused%20cells%20with%20formulae%20in).%20Can%20this%20be%20done%3F%3C%2FP%3E%3CP%3EMany%20thanks%20in%20anticipation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2060463%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2060594%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20sum%20a%20column%20where%20some%20cells%20contain%20formulae%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2060594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928919%22%20target%3D%22_blank%22%3E%40Prober22%3C%2FA%3E%26nbsp%3BTry%20an%20IFERROR%20function%20to%20the%20cells%20that%20give%20you%20the%20error.%20More%20info%20can%20be%20read%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fiferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E%3C%2FP%3E%3CP%3EThe%20formula%20would%20look%20something%20like%20this%3A%20IFERROR(B2%2FC2%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a sheet with multiple columns containing data. In one column, Q, I have a formula that, when data is entered elsewhere within the document, the cell formula becomes a value. Not all cells are populated with values, so the "unused" cells contain  "#DIV/0!" until the data is entered elsewhere. I want to be able to sum the column in Q155, but only include the cells containing values (i.e. excluding the "unused cells with formulae in). Can this be done?

Many thanks in anticipation.

2 Replies

@Prober22 Try an IFERROR function to the cells that give you the error. More info can be read here

The formula would look something like this: IFERROR(B2/C2,0)

@adversi 

 

Many thanks for your help - that solved my problem.

Best wishes.