Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUM function (2000)

    Sorry to be a forum post hog, but I am completely baffled by the following behavior:

    I have a form containing comboboxes which are linked to cells A1 to A10 in worksheet A. In worksheet B I have cells which are linked to cells A1 to A10 in worksheet A (a hidden master sheet). Summing the linked cells in worksheet B results in a value of "0". The cells in both ranges are formatted as numbers with zero decimals. If I type a number in place of the link, then sum and sumif both work. Calculation is set to automatic.

    If I go to a new workbook and replicate the functions and links (without using the comboboxes on a form) both functions work properly.

    I am at my wits end trying to figure out what is keeping the sum and/or sumif functions from operating properly. Any ideas?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    Can you post an example Workbook showing the problem? I have a feeling that, even though you have formatted the cells as numbers, they are really text, but I can't tell without seeing the workbook.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    Legare, I will try but it is part of a 3 meg book that is so intertwined that I doubt if it will retain the integrity if I copy the sheet to a new one. Thanks.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    Legare, I was doubtful that the nature of the problem would be clear, but it seems to have persisted. The formula in question is in B11 of the first sheet. There are many external references still in the sheet, so it may prompt you for the file location. 99kb...here comes.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    Mike --

    Try:

    =SUMIF(SALESRECORD!C25:C40,TEXT(B3,"00000"),SALESR ECORD!D2540)

    the result of which proves that SALESRECORD!C25:C40 is text-formatted.

    PS. Enter, say, in G1 in the worksheet SALESRECORD

    =ISNUMBER(C25)

    It gives 0, meaning FALSE. Why is it that I get 0 or 1 with this function in this ws instead of FALSE or TRUE?

    Aladin
    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    Legare and Aladin are correct. Right click on the attachment in your post 109570 and open in a new window. look at the value in [Category Food]!B11. it should be zero. switch to SALESRECORD and in C29 type 51700. now switch back to [Category Food]!B11. when I do the above I now get 525. the formula in the cell before I overwrite it is ='http://www.wopr.com/Accounting 4.01Client[Accounting v4.011.xls]MasterSettings'!F37. that won't be how it looks to you but try changing it to something like =<font color=red>0+</font color=red>'http://www.wopr.com/Accounting 4.01Client[Accounting v4.011.xls]MasterSettings'!F37 - although this doesn't actually work for anyone using the copy posted because this then requires recalculation of the cell which in turn needs access to the referenced workbook - this will force a conversion from a text data type to a numeric data type.

    HTH

    Brooke

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    What you say is true, however, when I right click and go to formatting, it says the cells are number formatted. What's up with this?

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    Brooke, what would be causing this to change to text format within the context of the original workbook? If I right click, it says number format.

  9. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    the cell you are looking at may well be number formatted, but if you are pulling in text from another source via =ThisWorkbook!B11 then the actual data will have the datatype of ThisWorkbook!B11, and not the cell in which the formula is placed.

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    Thanks Brooke, I will backtrack and find out where it is happening. It worked fine until I put in some comboboxes and used the right() function to pull some of the selected values. I have a sneaking suspicion that this is where it originates.

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    =right() will indeed return a text datatype and not a numeric one. Happy Hunting!

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    It was the combobox/right() combination. The following fixed it and the whole sheets lights up like it used to.

    <pre>=IF(H3="",0,VALUE(RIGHT(H3,5)))
    </pre>


    Thanks Legare, Alladin, and Brooke!

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function (2000)

    The values in C25:C40 on sheet SalesRecord are Text not numbers and they are being compared to the numeric value in cell B3. Therefore you are not getting any equal conparisons. The format of a cell does not control what is in the cell. If you format a cell that contains text as a number, the text is not converted to a number, it just displays using the General format. Formatting a cell as a number just tells Excel what format to use if the cell contains a number, if it contains anything else Excel uses the General format. One of the few time that the cell format controls what is in the cell is when a value consisting of numeric digits is entered into a cell that is formatted as text. In this case, even though the value is a number, it will be stored in the cell as text.

    There are several ways around this problem which I can't test with your workbook since I don't have the MasterSettings workbook. You could use the Text() function in cell B3 to convert it to text, something like this:

    <pre>=Text('C:Accounting 4.01Client[Accounting v4.011.xls]MasterSettings'!B15,"00000")
    </pre>


    You could also use the Value() function in cells C25:C40 to convert them to numbers, something like this:

    <pre>=VALUE('C:Accounting 4.01Client[Accounting v4.011.xls]MasterSettings'!F33)
    </pre>

    Legare Coleman

Posting Permissions

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