+ Reply to Thread
Results 1 to 7 of 7

Column Totals

  1. #1
    Registered User
    Join Date
    07-09-2009
    Location
    Warrington, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Column Totals

    Hello,
    newbie here not that savvy with Excel. I need help to total a column but in four cells the values are as a result of conditional formatting and the total ignores those cells. Is this difficult or am I a bit slow? This is in 2003.

    thank you in advance.
    Last edited by tomtomal; 07-09-2009 at 05:21 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Column Totals

    Are those 4 cells text rather than real numbers...

    what is the conditional formula in those 4 cells.. and what is your summing formula?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    07-09-2009
    Location
    Warrington, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Column Totals

    I never realised that the cells could be text.
    The formatted cells are slightly different but use this format:
    =IF(B4="Jan","23.00",IF(B4="April","23.00",IF(B4="July","23.00",IF(B4="Oct","23.00"," 0.00 "))))
    The total is a simple range.
    =SUM(B9:B21)
    I know that this might not be very elegant but I am a learner
    thanks again

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Column Totals

    Yes those are returning text strings "23.00" and "0.00"


    change formula to:

    =IF(OR(B4={"Jan","April","July","Oct"}),23,0)

    then you can format the cell through Format|Cells as Number with 2 decimals...

  5. #5
    Registered User
    Join Date
    07-09-2009
    Location
    Warrington, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Column Totals

    Thank you that works superbly.
    Where can I learn formula's like that?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Column Totals

    Really, by sticking around here and following the questions and answers here.. most of the formulas/techniques here are not presented in most books, general Excel websites...they are built up from the basics, but "played around" with to get some specific solutions, etc. Also, follow or google some of the links we suggest here and there, like Contextures, Peltier, , etc to see special excel sites and articles...

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Column Totals

    Please also mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1