• 410K Members
• 9,443 Online
• 466K Conversations
SOLVED

New Contributor

# 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.

3 Replies

# Re: Excel auto sum not working on values calculated using formula.

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

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

Solution

# ISSUE Resolved

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

# Re: ISSUE Resolved

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