SUM function (2000)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Mike —

Try:

=SUMIF(SALESRECORD!C25:C40,TEXT(B3,”00000″),SALESRECORD!D25:D40)

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

Reply | Quote

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?

Reply | Quote

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 =0+‘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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

=IF(H3="",0,VALUE(RIGHT(H3,5)))

Thanks Legare, Alladin, and Brooke!

Reply | Quote

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:

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

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

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

Reply | Quote