Hello.
I want to sum the prices of the stuffs that are in columns B2 to B7 in Sheet 1 and Sheet 2, but apparently my formula is wrong!
=SUM(sheet1:sheet2!B2:B7)
Would you please help me?
Thank you.
PRICE.ods (15.1 KB)
Itâs not Excel which would work there if formula were =SUM(Sheet1:Sheet1_2!B2:B7)
For simplicity try =SUM(B2:B7;$Sheet1_2.B2:B7)
or if you want to specify both sheet names =SUM($Sheet1.B2:B7;$Sheet1_2.B2:B7)
1 Like
Let me ask again.
I want to SUM total price in B2:B7 in Sheet 1 and Sheet 2 too and Sheet 3 too.
Please see my files.
PRICE.ods (15.2 KB)
When you provide a range, you define two its opposite corners. For 1D ranges, itâs top and bottom cells (like sheet1.A1:A10); 2D ranges need top left and bottom right corners (sheet1.A1:B10).
For 3D ranges (cuboids, where adjacent sheets form the third dimension of the range) for functions that can take them, you also define opposite corners:
=SUM(sheet1.B2:sheet2.B7)
i.e., the top left corner on the first sheet, and the bottom right corner on the last sheet.
Or you activate Excel formula syntax (Options|Calc|Formula), and use the syntax as you shown initially.
3 Likes
Please see My screenshot from your formula.
Would you please update My file (in reply 2) with your formula?
Indeed. Solution is simple: use correct sheet names. When your sheet name contains a space (like Sheet 2), it is incorrect to thing that sheet2 will work.
Your file uses Excel notation.
=SUM(Sheet1:'Sheet 2'!B2:B7)I corrected the formula butâŚ!
Please see screenshot.
Would you please correct attached file that has new frmula?
Thank you.
PRICE.ods (15.1 KB)
Uh? Did you read my comment? Did you simply copy the provided formula
=SUM(Sheet1:'Sheet 2'!B2:B7)
which is different to your
=SUM(Sheet1!B2:sheet 2.b7)
and did you read this piece:
I Correct My firmula with this formula:
=SUM(Sheet1.B2:B7:Sheet2.B2:B7)
and works as fine.
sorry for My mistake and thank you for your reply.
By accident because you donât know what you are doing and also didnât follow the correct advices Mike gave. That expression extends the range Sheet1.B2:B7 with the range Sheet2.B2:B7 to form the resulting range Sheet1.B2:Sheet2.B7, which is what Mike told you to use, and apparently you renamed Sheet 2 to Sheet2.
Please donât be angry. I am newbie in LibreOffice and my English is also weak.