SOLVED
Home

Excel auto sum not working on values calculated using formula.

%3CLINGO-SUB%20id%3D%22lingo-sub-311222%22%20slang%3D%22en-US%22%3EExcel%20auto%20sum%20not%20working%20on%20values%20calculated%20using%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-311222%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20create%20a%20formula%20for%20SUM%20and%20AVERAGE%20that%20has%20a%20variable%20number%20of%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20example%20where%20the%20%3CSTRONG%3E444%3C%2FSTRONG%3Ecount%20varies%20from%20worksheet%20to%20worksheet.%26nbsp%3B%20I%20have%20saved%20this%26nbsp%3Bformula%20and%20easy%20enough%20to%20use%20but%20want%20to%20develop%20something%20similar%20but%20with%20many%20more%20columns.%26nbsp%3B%20Worth%20some%20time%20and%20effort%20as%20I%20evaluate%20the%20averages%20many%20times%20a%20week%20as%20they%20can%20change%20daily.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(V3%3AV444)%2F(SUM(CC3%3ACC444)%2BSUM(CD3%3ACD444))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-311222%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-312597%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20auto%20sum%20not%20working%20on%20values%20calculated%20using%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312597%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much.%26nbsp%3B%20I%20have%20each%20column%20working%20perfectly%20and%20just%20need%20to%20put%20it%20all%20together%20with%20correct%20number%20of%20parentheses.%3C%2FP%3E%3CP%3EBest%2C%3C%2FP%3E%3CP%3EFred%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-312229%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20auto%20sum%20not%20working%20on%20values%20calculated%20using%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312229%22%20slang%3D%22en-US%22%3E%3CP%3EInstead%20of%20SUM(V3%3AV444)%20it%20could%20be%20something%20like%3C%2FP%3E%0A%3CPRE%3E%3DSUM(OFFSET(V3%2C0%2C0%2CCOUNTA(V3%3AV10000)))%3C%2FPRE%3E%0A%3CP%3Eetc%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-312172%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20auto%20sum%20not%20working%20on%20values%20calculated%20using%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312172%22%20slang%3D%22en-US%22%3EI%20will%20send%20the%20whole%20spreadsheet%20tomorrow%20and%20not%20sure%20what%20else%20I%20can%20say.%20It%20is%20a%20list%20of%20hundreds%20of%20home%20sales%20with%2040%20or%20so%20fields%20of%20information.%20I%20want%20to%20use%20the%20sums%20of%204%20or%205%20columns%20(fields)%20for%20averages%20between%202%20or%203%20fields%20at%20a%20time.%20For%20example-divide%20sum%20of%20one%20field%20by%20sum%20of%20another%20add%20three%20of%20those%20averages%20together%20and%20divide%20by%20the%20number%20of%20fields%20used.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-311226%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20auto%20sum%20not%20working%20on%20values%20calculated%20using%20formula.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-311226%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3ECan%20you%20give%20a%20bit%20more%20information%20about%20what%20you%20need%2C%20ideally%20attaching%20an%20example%3F%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I want to create a formula for SUM and AVERAGE that has a variable number of rows.

 

Here is the example where the 444 count varies from worksheet to worksheet.  I have saved this formula and easy enough to use but want to develop something similar but with many more columns.  Worth some time and effort as I evaluate the averages many times a week as they can change daily.

 

=SUM(V3:V444)/(SUM(CC3:CC444)+SUM(CD3:CD444))

4 Replies
Highlighted
Hi
Can you give a bit more information about what you need, ideally attaching an example?
Highlighted
I will send the whole spreadsheet tomorrow and not sure what else I can say. It is a list of hundreds of home sales with 40 or so fields of information. I want to use the sums of 4 or 5 columns (fields) for averages between 2 or 3 fields at a time. For example-divide sum of one field by sum of another add three of those averages together and divide by the number of fields used.
Highlighted
Solution

Instead of SUM(V3:V444) it could be something like

=SUM(OFFSET(V3,0,0,COUNTA(V3:V10000)))

etc

 

Highlighted

Thank you so much.  I have each column working perfectly and just need to put it all together with correct number of parentheses.

Best,

Fred

Related Conversations
Fonction si ?
LolitaG in Excel on
0 Replies
How do I do this?
moppy1234 in Excel on
3 Replies
FORMULA ASSISTANCE
Bran20 in Excel on
2 Replies
Excel formula auto complete not working
Bholeswar in Excel on
0 Replies
How to Include a Text Field in an Excel Pivot Table
tdc-studio in Excel on
2 Replies
Excel taking forever to launch and very slow
pjvilloud in Excel on
2 Replies