Numbers Sum -- wrong answer

Did you enter the money amounts as shown (eg, -$4,365.40) ?

If so, those 'numbers' will be interpreted as text values. SUM, and most other functions dealing with 'numbers,' will assign a zero value to any text strings encountered in calculating the sum, which would explain the zero sum at the top of the column (but not explain wht it is expressed as $0.00).

Regards,

Barry

Are the six rows (44, 70, 86, 97, 112, 130) identified in your 'formula giving the right answer' th only rows to contain the word "Total" in column H?

If so, try this:

SUMIF(H,"=*Total",G)

Regards,

Barry

EDIT: Corrected syntax!

B

I suggest unhiding all the rows and provide a screenshot showing everything in the columns (if possible)

If you typed the "$" in any of the cells, then that may be your problem. I suggest formatting the column I as a Number (not currency for now) AND make sure to uncheck the thousands separator (the comma)

The SUM in your formula includes the values in 87 cells that are hidden in your screenshots. Are you sure that the values in those cells sum to zero?

Some diagnostic questions:

Are the cells in column G set to automatic alignment or to Align Right?

Same question for column I.

Are the values shown in column G directly from the CSV file, or are they generated by a formula in the table?

If direct from CSV, what is the appearance of the Insurnce total value when the CSV file is opened in TextEdit?

If you add a column J to the table and populate the pictured rows with the formula I + 2 or I43+2, what results do you get?

Regards,

Barry

Thanks for providing screenshots. Unfortunately, they don't show the column and row numbers, so it is difficult to know which is cell I43 and what result you are expecting.

SG

Hi Eden,

One of the consistent things about Automatic is that it aligns numeric (and quasi numeric—eg. Date & Time) values right and non-numeric values left,

Regards,

Barry

Good to know. Thanks, Barry

'The answer is, "No".'

Assuming this is the response to "Are the six rows (44, 70, 86, 97, 112, 130) identified in your 'formula giving the right answer' the only rows to contain the word "Total" in column H?", are these six rows the only rows to contain " Total" (note the leading space) in column H.

What I'm looking for here is a way to distinguish these rows from all of the rows whose column G numbers should not be included in the sum.

If they are the only rows in which Total is preceded by a space, then the formula above, with a space inserted between the asterisk and the T should work.

If all of the rows containing the various subtotals have labels in column H that end with "Subtotal," then a SUMIFS formula with two conditions should work:

Both formulas shown SUM the values in rows 4, 11 and 13.

The first does two tests: does the label NOT end with "subtotal" and does the label end with "total". Both tests must return TRUE for the value to be included in the total.

The second does a single test: does the label end with " total". Ths returns TRUE only for the rows including a space before "total" at the end of the label. As the subtotal rows do not include such a space, they, and the rows not ending in total are rejected.

These are alternate solutions to your method of manually extracting the Total row values from the list.

"Why the formula I used is producing zero is what I'm trying to discover."

As am I, and as, I expect, are SGIII and Jeff.

Going up the thread a bit, what are the answers to the diagnostic questions I asked on September 7? The purpose of these questions was to gather some clues to "what [you're] trying to discover.

Regards,

Barry

I won't give a blow by blow account. I tried nearly every suggestion. It turns out that a combination of Barry's and Wayne's ideas fixed the problem. After formatting every row to be SUMmed as a number, not currency, the problem formula produced the correct result. (Yay!)

The values in the exported CSV file came formatted as currency, but apparently were in fact text values with exported dollar signs and commas. The only original hint - the possible significance off which I missed - was that the values were left justified. I didn't think much of it because numbers and currency can also be formatted to be left justified, and the data format field in the side bar read "automatic", which in this case was less than helpful.

(Also as usual, Help wasn't.)

Thank you.