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
Highlighted
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
Highlighted

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

 

Highlighted
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 ))

 

Highlighted

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
IF statements and conditional formatting
clare1981 in Excel on
0 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
0 Replies
Sumifs
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
3 Replies