+ Reply to Thread
Results 1 to 12 of 12

Simple SUM function not adding numbers resulting from IF formula

  1. 05-05-2014, 05:49 AM #1
    protein6x
    protein6x is offline
    Registered User
    Join Date
    03-13-2014
    Location
    Slough
    MS-Off Ver
    Excel 2010
    Posts
    5

    Simple SUM function not adding numbers resulting from IF formula

    Hi,

    I have the below formula in the cell C492
    =IF(B490="MORTGAGE","622.38", IF(B490="TV LICENCE","12.12", IF(B490="THAMES WATER","23.51"," ")))

    where when I type MORTGAGE in cell B490, the number 622.38 automatically pops up in C492.

    Now, I have an added total for column C with simple sum function as

    =SUM(C23:C500) which adds the numbers when I enter the nimbers manually in column C. However, it doesn't add any numbers resulting from the formula i.e. when I type mortgage, then 622.38 comes in and doesn't get added and so on for TV LICENCE etc...

    The whole column C is formatted as 'numbers'. Please help.

    Thanks in advance.
    Reply With QuoteRegister To Reply
  2. 05-05-2014, 05:51 AM #2
    Saarang84
    Saarang84 is offline
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Simple SUM function not adding numbers resulting from IF formula

    Can u post a sample workbook with insensitive data?
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang
    Reply With QuoteRegister To Reply
  3. 05-05-2014, 06:08 AM #3
    protein6x
    protein6x is offline
    Registered User
    Join Date
    03-13-2014
    Location
    Slough
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Simple SUM function not adding numbers resulting from IF formula

    Hi Sarang, please see this... (Tried to make it as clear as possible)

    XL SAMPLE.jpg
    Reply With QuoteRegister To Reply
  4. 05-05-2014, 06:14 AM #4
    Saarang84
    Saarang84 is offline
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Simple SUM function not adding numbers resulting from IF formula

    Post the workbook with mocked data if your data is sensitive, I'll look into it. Do not post images, as it would not be ideal for us to replicate it.
    Reply With QuoteRegister To Reply
  5. 05-05-2014, 06:24 AM #5
    protein6x
    protein6x is offline
    Registered User
    Join Date
    03-13-2014
    Location
    Slough
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Simple SUM function not adding numbers resulting from IF formula

    SAMPLE SHEET 1.xlsxSAMPLE SHEET 1.xlsx
    Reply With QuoteRegister To Reply
  6. 05-05-2014, 06:25 AM #6
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Simple SUM function not adding numbers resulting from IF formula

    Hello protein6x you can try as you want to avoid only TV, Radio and Mobile as per your formula
    copy paste below in c2
    =SUMPRODUCT(--(B3:B11<>"TV")*(B3:B11<>"Radio")*(B3:B11<>"MOBILE")*(C3:C11))

    Or if you have only Expense Keyword in column B other than TV, Radio and Mobile then you could also use
    =SUMIF(B3:B500,"*"&"Expense"&"*",C3:C500) in C2
    Last edited by hemesh; 05-05-2014 at 06:30 AM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh
    Reply With QuoteRegister To Reply
  7. 05-05-2014, 06:28 AM #7
    protein6x
    protein6x is offline
    Registered User
    Join Date
    03-13-2014
    Location
    Slough
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Simple SUM function not adding numbers resulting from IF formula

    Hi Hamesh,

    Just to make it clear, I want all numbers from C column to be added and nothing excluded. Thanks for your kind reply.
    Reply With QuoteRegister To Reply
  8. 05-05-2014, 06:34 AM #8
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Simple SUM function not adding numbers resulting from IF formula

    Ok now got it change your if to below

    =IF(B490="MORTGAGE",622.38, IF(B490="TV LICENCE",12.12, IF(B490="THAMES WATER",23.51," ")))

    because double quote is converting the numbers to the text so sum is not able to count it
    Reply With QuoteRegister To Reply
  9. 05-05-2014, 06:34 AM #9
    Saarang84
    Saarang84 is offline
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Simple SUM function not adding numbers resulting from IF formula

    Hi,

    Your formula (given below is incorrect), places the values 1200, 2000 and 500 as string values instead of numbers. So, in rows 9 to 11, though they are shown, they are not added to the main sum formula.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Correct Usage :
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 05-05-2014, 06:37 AM #10
    MOONDRIFT47
    MOONDRIFT47 is offline
    Registered User
    Join Date
    11-26-2011
    Location
    Yeovil, england
    MS-Off Ver
    365
    Posts
    73

    Re: Simple SUM function not adding numbers resulting from IF formula

    Hi,

    Just change your formula to have no "" round the numbers i.e. 2000 instead of "2000"
    Reply With QuoteRegister To Reply
  11. 05-05-2014, 06:39 AM #11
    protein6x
    protein6x is offline
    Registered User
    Join Date
    03-13-2014
    Location
    Slough
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Simple SUM function not adding numbers resulting from IF formula

    Hi Saarang, That's working. Thanks a lot. Much appreciated.
    Reply With QuoteRegister To Reply
  12. 05-05-2014, 06:45 AM #12
    Saarang84
    Saarang84 is offline
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Simple SUM function not adding numbers resulting from IF formula

    You are welcome.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Simple MATCH formula, resulting in Multi Returns.
    By JoeBlakkk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-01-2011, 05:44 PM
  2. [SOLVED] How to replace a function with its resulting reference in a formula?
    By Dmitry Kopnichev in forum Excel General
    Replies: 7
    Last Post: 10-13-2005, 05:05 PM
  3. How to replace a function with its resulting reference in a formula?
    By Dmitry Kopnichev in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-13-2005, 05:05 PM
  4. [SOLVED] How to replace a function with its resulting reference in a formula?
    By Dmitry Kopnichev in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-13-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1