Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Wrong value calculated and an error in my formula

I have 2 tables. Table 1 has a single header column and row. Cells B2:F6 are formatted as numbers.


In Table 2, I'm trying to sum the values from two columns from Table 1. But, I get an error message in one cell (B1) and the incorrect value in the other cell (B2). I confirmed the format in Table 1 B2:F6 are formatted as numbers.


Any ideal on what I'm doing wrong?

MacBook Pro 15″, macOS 11.6

Posted on Sep 8, 2022 3:06 PM

Reply
Question marked as Best answer

If you are trying to sum the column Table 1::B and Table 1::C, the formula is

=SUM(Table 1::B, Table 1::C)

It should have a comma, not a +


A "whole column" reference like Table 1::B can mean the entire column or it could mean "the cell in Table 1::B in the same row as this formula". If Table 1::B is used in a formula in a spot that requires a single cell, it will be treated as a single cell. If it could be a range or a single cell, it is interpreted as a range. Same goes for the reference to column C. In your formula you have Table 1::B + C. You cannot use + on a range so both of those references are single cells. Your formula is in row 1 so it is the same as Table 1::B1 + Table 1::C1


Same for the formula in row 2. SUM(E+F) is the same as SUM(E2+F2). You want SUM(E,F)


It is a lot easier to provide help when "use header names as references" is unselected. That "C" reference threw me off for a while. I thought it was column C in Table 2 (which does not exist) but it is actually the column in Table 1 that has a "C" in its header row (which happens to be column C in that table)

Posted on Sep 8, 2022 3:49 PM

3 replies
Question marked as Best answer

Sep 8, 2022 3:49 PM in response to DaThiryPointBuck

If you are trying to sum the column Table 1::B and Table 1::C, the formula is

=SUM(Table 1::B, Table 1::C)

It should have a comma, not a +


A "whole column" reference like Table 1::B can mean the entire column or it could mean "the cell in Table 1::B in the same row as this formula". If Table 1::B is used in a formula in a spot that requires a single cell, it will be treated as a single cell. If it could be a range or a single cell, it is interpreted as a range. Same goes for the reference to column C. In your formula you have Table 1::B + C. You cannot use + on a range so both of those references are single cells. Your formula is in row 1 so it is the same as Table 1::B1 + Table 1::C1


Same for the formula in row 2. SUM(E+F) is the same as SUM(E2+F2). You want SUM(E,F)


It is a lot easier to provide help when "use header names as references" is unselected. That "C" reference threw me off for a while. I thought it was column C in Table 2 (which does not exist) but it is actually the column in Table 1 that has a "C" in its header row (which happens to be column C in that table)

Wrong value calculated and an error in my formula

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.