+ Reply to Thread
Results 1 to 17 of 17

Need Help Summing a Column that Contains Nested IF Functions

  1. 12-10-2019, 11:41 AM #1
    ImStuckPeaseHelpMe
    ImStuckPeaseHelpMe is offline
    Registered User
    Join Date
    12-10-2019
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    10

    Unhappy Need Help Summing a Column that Contains Nested IF Functions

    I am trying to sum Column D into cell N3.

    In Column C I put the name of the product. Then in Column D I put a nested IF Function in order to automatically put the price into the cells in Column D based on what product is selected in Column C.

    The problem I am running into now is when I try to =Sum Column D, I'm getting an output of $0.00. I tried formatting Cells in Column D to General, Number, and Currency but none of the fixed it to do a correct summation.

    Can someone help me reformat the formula in order to sum properly while still automatically putting the number in based off the name of the product selected in Column C.
    Attached Files Attached Files
    Last edited by ImStuckPeaseHelpMe; 12-10-2019 at 11:59 AM.
    Reply With QuoteRegister To Reply
  2. 12-10-2019, 11:51 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Need Help Summing a Column that Contains Nested IF Functions

    Hi
    in your example there is no formula in col C.
    Otherwise summing col D ( cell N3) works as it should. I can't find a problem there
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 12-10-2019, 11:51 AM #3
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Need Help Summing a Column that Contains Nested IF Functions

    I don't see any if functions in column D, just a value 99.50 which is also the value in your sum function in cell N3.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  4. 12-10-2019, 12:01 PM #4
    ImStuckPeaseHelpMe
    ImStuckPeaseHelpMe is offline
    Registered User
    Join Date
    12-10-2019
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Need Help Summing a Column that Contains Nested IF Functions

    Oops Apologies. I uploaded an older version of the file. I just re-attached the correct file now with the nest IF formula i'm trying to use in D5.
    Reply With QuoteRegister To Reply
  5. 12-10-2019, 12:02 PM #5
    ImStuckPeaseHelpMe
    ImStuckPeaseHelpMe is offline
    Registered User
    Join Date
    12-10-2019
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Need Help Summing a Column that Contains Nested IF Functions

    I uploaded an older version of the file. I just re-attached the correct file with the formula in D5.
    Reply With QuoteRegister To Reply
  6. 12-10-2019, 12:03 PM #6
    ImStuckPeaseHelpMe
    ImStuckPeaseHelpMe is offline
    Registered User
    Join Date
    12-10-2019
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Need Help Summing a Column that Contains Nested IF Functions

    Sorry about that. That was an old file version. I just uploaded the most current with the formula in cell D5 that i'm trying to use for the whole D Column.
    Reply With QuoteRegister To Reply
  7. 12-10-2019, 12:04 PM #7
    Pete_UK
    Pete_UK is online now
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,724

    Re: Need Help Summing a Column that Contains Nested IF Functions

    You have quotes around your $99.50 and $87.50 in that formula, and these will be returned as text values which sum to zero. Remove those quotes around the numbers, then the formula will return numeric values instead.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  8. 12-10-2019, 12:06 PM #8
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Need Help Summing a Column that Contains Nested IF Functions

    In your formula =IF(C5="";"";IF(C5="Brightening C+ Trial Regime"; "$99.50";IF(C5="Revision Starter Trial Regime";"$87.50";IF(C5="Pre & Post Procedure Trial Regime";"$99.50";IF(C5="Injection Perfection Trial Regime";"$87.50";"")))))
    remove the double quotes around the prices, double quotes make them strings
    Alternatively use the VLOOKUP function, much more versatile
    Reply With QuoteRegister To Reply
  9. 12-10-2019, 12:06 PM #9
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Need Help Summing a Column that Contains Nested IF Functions

    you put text as the output in D5. your formula...
    =IF(C5="","",IF(C5="Brightening C+ Trial Regime", "$99.50",IF(C5="Revision Starter Trial Regime","$87.50",IF(C5="Pre & Post Procedure Trial Regime","$99.50",IF(C5="Injection Perfection Trial Regime","$87.50","")))))
    the quotes around the values "$99.50" and same for the other "values" are making it text, excel doesn't know how to add text. It would be similar to asking what the sum of a column of Cat.
    remove the quotes and your sum formula and cost formula in col D will both work fine.
    Reply With QuoteRegister To Reply
  10. 12-10-2019, 12:08 PM #10
    Pete_UK
    Pete_UK is online now
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,724

    Re: Need Help Summing a Column that Contains Nested IF Functions

    Also, you don't need the $ symbol either, as the cell is formatted to show these.

    Pete
    Reply With QuoteRegister To Reply
  11. 12-10-2019, 12:11 PM #11
    ImStuckPeaseHelpMe
    ImStuckPeaseHelpMe is offline
    Registered User
    Join Date
    12-10-2019
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Need Help Summing a Column that Contains Nested IF Functions

    That worked! Thank you so much!
    Reply With QuoteRegister To Reply
  12. 12-10-2019, 12:12 PM #12
    ImStuckPeaseHelpMe
    ImStuckPeaseHelpMe is offline
    Registered User
    Join Date
    12-10-2019
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Need Help Summing a Column that Contains Nested IF Functions

    Thank you Pete! I removed the quotes and the dollar sign and it all functions properly now. Much appreciated!
    Reply With QuoteRegister To Reply
  13. 12-10-2019, 12:13 PM #13
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Need Help Summing a Column that Contains Nested IF Functions

    Inserting your data in a table will make maintenance much easier
    Reply With QuoteRegister To Reply
  14. 12-10-2019, 12:13 PM #14
    ImStuckPeaseHelpMe
    ImStuckPeaseHelpMe is offline
    Registered User
    Join Date
    12-10-2019
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Need Help Summing a Column that Contains Nested IF Functions

    Added positive rep to all those who helped!
    Reply With QuoteRegister To Reply
  15. 12-10-2019, 12:15 PM #15
    Pete_UK
    Pete_UK is online now
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,724

    Re: Need Help Summing a Column that Contains Nested IF Functions

    Thanks for doing that.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete
    Reply With QuoteRegister To Reply
  16. 12-10-2019, 12:17 PM #16
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Need Help Summing a Column that Contains Nested IF Functions

    Glad we could help, AND thank you for the rep!
    Reply With QuoteRegister To Reply
  17. 12-11-2019, 04:01 AM #17
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Need Help Summing a Column that Contains Nested IF Functions

    Quote Originally Posted by ImStuckPeaseHelpMe View Post
    Added positive rep to all those who helped!
    Ah, then my posts were worthless..
    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. Creating multiple IF Functions with some nested IF functions in false value return
    By jakecutler in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-17-2015, 01:35 AM
  2. Summing functions not working
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-12-2015, 01:44 AM
  3. Summing Cells with Functions
    By mtsprink52 in forum Excel General
    Replies: 2
    Last Post: 10-16-2014, 10:20 PM
  4. prioritizing budgets with functions and then summing
    By jenjen10 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-26-2014, 12:06 PM
  5. [SOLVED] Summing the results of nested IFs
    By stevejfice in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-01-2012, 11:59 AM
  6. Alternative to summing nested If's
    By mashley in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-09-2009, 09:37 AM
  7. Summing Nested If Statements
    By bruehl10 in forum Excel General
    Replies: 1
    Last Post: 05-15-2008, 12:09 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