SOLVED
Home

Excel auto sum not working on values calculated using formula.

%3CLINGO-SUB%20id%3D%22lingo-sub-307037%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-307037%22%20slang%3D%22en-US%22%3E%3CP%3EI%20created%20a%20formula%20to%20calculate%20charges%20and%20it's%20working%20correctly%2C%20but%20auto%20sum%20is%20not%20working%20on%20these%20cells.%20I%20already%20checked%20and%20the%20cell%20format%20type%20is%26nbsp%3BGeneral.%26nbsp%3BI%20have%20attached%20the%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-307037%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307420%22%20slang%3D%22en-US%22%3ERe%3A%20ISSUE%20Resolved%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307420%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20your%20updated%20formula%3C%2FP%3E%0A%3CP%3E1)%20You%20return%20zero%20as%20text%2C%20better%20as%20number.%20Even%20if%20that%20doesn't%20affect%20SUM%2C%20it%20affects%20formatting.%20Better%20to%20have%20all%20numbers%20in%20resulting%20column%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20Instead%20of%20DATEVALUE%20with%20date%20string%20better%20to%20use%20DATE%20or%20DATEVALUE%20with%20reference%20on%20another%20cell.%20If%20you%20open%20your%20file%20on%20PC%20with%20another%20locale%20or%20another%20default%20date%20settings%20in%20OS%2C%20you%20formula%20won't%20work%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E3)%20Not%20necessary%20to%20complicate%20the%20formula%20with%20DATEDIF%20to%20find%20difference%20in%20days.%20Dates%20in%20Excel%20are%20just%20integers%2C%20simple%20subtraction%26nbsp%3Bis%20enough.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307400%22%20slang%3D%22en-US%22%3EISSUE%20Resolved%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307400%22%20slang%3D%22en-US%22%3E%3CPRE%3E%3DIF(%20EXACT(E9%2C%22Not%20Installed%22)%2C%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E%220%22%3C%2FSTRONG%3E%3C%2FFONT%3E%2C%20IF(%20(E9%26gt%3BDATEVALUE(%221-Oct-18%22))%2C%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E%2250.00%22%3C%2FSTRONG%3E%3C%2FFONT%3E%2C(DATEDIF(E9%2CDATEVALUE(%2231-Oct-18%22)%2C%22d%22)%2B1)*1.61%20))%3C%2FPRE%3E%3CP%3Ehad%2050%20in%20%22%22%20and%20therefore%20it%20was%20text.%20Removing%20double%20quotes%20fixed%20the%20issue.%3C%2FP%3E%3CPRE%3E%3DIF(%20EXACT(E9%2C%22Not%20Installed%22)%2C%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E%220%22%3C%2FSTRONG%3E%3C%2FFONT%3E%2C%20IF(%20(E9%26gt%3BDATEVALUE(%221-Oct-18%22))%2C%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E50.00%3C%2FSTRONG%3E%3C%2FFONT%3E%2C(DATEDIF(E9%2CDATEVALUE(%2231-Oct-18%22)%2C%22d%22)%2B1)*1.61%20))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307045%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-307045%22%20slang%3D%22en-US%22%3E%3CP%3EFormat%20is%20General%2C%20but%20you%20return%20text%20by%20your%20formula%3C%2FP%3E%0A%3CPRE%3E%3DIF(%20EXACT(E9%2C%22Not%20Installed%22)%2C%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E%220%22%3C%2FSTRONG%3E%3C%2FFONT%3E%2C%20IF(%20(E9%26gt%3BDATEVALUE(%221-Oct-18%22))%2C%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E%2250.00%22%3C%2FSTRONG%3E%3C%2FFONT%3E%2C(DATEDIF(E9%2CDATEVALUE(%2231-Oct-18%22)%2C%22d%22)%2B1)*1.61%20))%3C%2FPRE%3E%0A%3CP%3EUse%20numbers%20instead%3C%2FP%3E%0A%3CPRE%3E%3DIF(%20EXACT(E9%2C%22Not%20Installed%22)%2C0%2C%20IF(%20(E9%26gt%3BDATE(2018%2C10%2C1))%2C50%2C(DATE(2018%2C10%2C31)-E9%2B1)*1.61%20))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
sac46280
New Contributor

I created a formula to calculate charges and it's working correctly, but auto sum is not working on these cells. I already checked and the cell format type is General. I have attached the file.

3 Replies

Format is General, but you return text by your formula

=IF( EXACT(E9,"Not Installed"),"0", IF( (E9>DATEVALUE("1-Oct-18")),"50.00",(DATEDIF(E9,DATEVALUE("31-Oct-18"),"d")+1)*1.61 ))

Use numbers instead

=IF( EXACT(E9,"Not Installed"),0, IF( (E9>DATE(2018,10,1)),50,(DATE(2018,10,31)-E9+1)*1.61 ))

 

Solution
=IF( EXACT(E9,"Not Installed"),"0", IF( (E9>DATEVALUE("1-Oct-18")),"50.00",(DATEDIF(E9,DATEVALUE("31-Oct-18"),"d")+1)*1.61 ))

had 50 in "" and therefore it was text. Removing double quotes fixed the issue.

=IF( EXACT(E9,"Not Installed"),"0", IF( (E9>DATEVALUE("1-Oct-18")),50.00,(DATEDIF(E9,DATEVALUE("31-Oct-18"),"d")+1)*1.61 ))

 

In your updated formula

1) You return zero as text, better as number. Even if that doesn't affect SUM, it affects formatting. Better to have all numbers in resulting column

 

2) Instead of DATEVALUE with date string better to use DATE or DATEVALUE with reference on another cell. If you open your file on PC with another locale or another default date settings in OS, you formula won't work

 

3) Not necessary to complicate the formula with DATEDIF to find difference in days. Dates in Excel are just integers, simple subtraction is enough.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies