Cannot Sum Summed cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1735550%22%20slang%3D%22en-US%22%3ECannot%20Sum%20Summed%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735550%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20using%20excel%20for%2020%20years%20and%20never%20had%20this%20problem%20before.%20I%20do%20these%20calculations%201x%20per%20month%20for%20the%20past%205%20years%2C%20this%20is%20the%20first%20time%20I've%20had%20trouble%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20summed%20various%20cells.%20I%20now%20need%20to%20sum%20those%20cells.%20I%20cannot%20sum%20the%20summed%20cells.%20How%20can%20I%20sum%20the%20cells%20that%20have%20already%20been%20summed%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20value%20of%20C157%20is%201.%3C%2FP%3E%3CP%3EThe%20value%20of%20C158%2C%20is%20the%20sum%20of%20C2%3AC157%20%3D%20108%3C%2FP%3E%3CP%3EI%20now%20want%20to%20sum%20C157%3AC158%2C%20but%20I%20get%20a%20value%20of%201%2C%20it%20should%20be%20109.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20get%20it%20to%20see%20the%20summed%20value%20in%20cell%20C157%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1735550%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1735847%22%20slang%3D%22en-US%22%3ERe%3A%20Cannot%20Sum%20Summed%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735847%22%20slang%3D%22en-US%22%3E%3CP%3EUpdate%3A%3C%2FP%3E%3CP%3ESheet%202%20is%20working%20correctly%20and%20SUM%20is%20working%20like%20normal%2C%20but%20Sheet%201%20is%20not.%20Sheet%202%20is%20an%20exact%20copy%20of%20Sheet%201%2C%20so.......%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1735850%22%20slang%3D%22en-US%22%3ERe%3A%20Cannot%20Sum%20Summed%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F816703%22%20target%3D%22_blank%22%3E%40globewandrer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20SUBTOTAL%20function%20ignores%20cells%20that%20also%20contain%20a%20SUBTOTAL%20formula.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20would%20sum%20C157%20and%20C158%2C%20you%20would%20count%20C157%20twice%2C%20since%20C158%20is%20the%20sum%20of%20C2%20to%20C157.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have been using excel for 20 years and never had this problem before. I do these calculations 1x per month for the past 5 years, this is the first time I've had trouble

 

I have summed various cells. I now need to sum those cells. I cannot sum the summed cells. How can I sum the cells that have already been summed?

 

The value of C157 is 1.

The value of C158, is the sum of C2:C157 = 108

I now want to sum C157:C158, but I get a value of 1, it should be 109.

 

How do I get it to see the summed value in cell C157

 

Thanks!

3 Replies
Highlighted

Update:

Sheet 2 is working correctly and SUM is working like normal, but Sheet 1 is not. Sheet 2 is an exact copy of Sheet 1, so.......?

Highlighted

@globewandrer 

The SUBTOTAL function ignores cells that also contain a SUBTOTAL formula.

 

If you would sum C157 and C158, you would count C157 twice, since C158 is the sum of C2 to C157.

Highlighted

@Hans Vogelaar 

 

I guess my issue then is Subtotal, which is what Excel is default using instead of Sum.  I select "Sum", but it uses "Subtotal" instead. Thanks for replying.