Forum Discussion
Excel auto sum not working on values calculated using formula.
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.
=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 ))
3 Replies
=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.
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 ))
