04 Column Formula with Totals
Create new Spreadsheet object
Set document properties
Add data
Create Table
Add Totals Row
Add Table to Worksheet
+-------+---------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------------------------------+
| A | B | C | D | E | F | G |
+----+-------+---------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------------------------------+
| 1 | Year | Country | Q1 | Q2 | Q3 | Q4 | Sales |
| 2 | 2010 | Belgium | 380 | 390 | 420 | 460 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 3 | 2010 | France | 510 | 490 | 460 | 590 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 4 | 2010 | Germany | 720 | 680 | 640 | 660 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 5 | 2010 | Italy | 440 | 410 | 420 | 450 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 6 | 2010 | Spain | 510 | 490 | 470 | 420 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 7 | 2010 | UK | 690 | 610 | 620 | 600 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 8 | 2010 | United States | 790 | 730 | 860 | 850 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 9 | 2011 | Belgium | 400 | 350 | 450 | 500 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 10 | 2011 | France | 620 | 650 | 415 | 570 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 11 | 2011 | Germany | 680 | 620 | 710 | 690 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 12 | 2011 | Italy | 430 | 370 | 350 | 335 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 13 | 2011 | Spain | 460 | 390 | 430 | 415 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 14 | 2011 | UK | 720 | 650 | 580 | 510 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 15 | 2011 | United States | 800 | 700 | 900 | 950 | =SUM(Sales_Data[[#This Row],[Q1]:[Q4]]) |
| 16 | Total | | =SUBTOTAL(109,Sales_Data[Q1]) | =SUBTOTAL(109,Sales_Data[Q2]) | =SUBTOTAL(109,Sales_Data[Q3]) | =SUBTOTAL(109,Sales_Data[Q4]) | =SUBTOTAL(109,Sales_Data[Sales]) |
+----+-------+---------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------------------------------+
Calculate Structured References
+-------+---------------+------+------+------+------+-------+
| A | B | C | D | E | F | G |
+----+-------+---------------+------+------+------+------+-------+
| 1 | Year | Country | Q1 | Q2 | Q3 | Q4 | Sales |
| 2 | 2010 | Belgium | 380 | 390 | 420 | 460 | 1650 |
| 3 | 2010 | France | 510 | 490 | 460 | 590 | 2050 |
| 4 | 2010 | Germany | 720 | 680 | 640 | 660 | 2700 |
| 5 | 2010 | Italy | 440 | 410 | 420 | 450 | 1720 |
| 6 | 2010 | Spain | 510 | 490 | 470 | 420 | 1890 |
| 7 | 2010 | UK | 690 | 610 | 620 | 600 | 2520 |
| 8 | 2010 | United States | 790 | 730 | 860 | 850 | 3230 |
| 9 | 2011 | Belgium | 400 | 350 | 450 | 500 | 1700 |
| 10 | 2011 | France | 620 | 650 | 415 | 570 | 2255 |
| 11 | 2011 | Germany | 680 | 620 | 710 | 690 | 2700 |
| 12 | 2011 | Italy | 430 | 370 | 350 | 335 | 1485 |
| 13 | 2011 | Spain | 460 | 390 | 430 | 415 | 1695 |
| 14 | 2011 | UK | 720 | 650 | 580 | 510 | 2460 |
| 15 | 2011 | United States | 800 | 700 | 900 | 950 | 3350 |
| 16 | Total | | 8150 | 7530 | 7725 | 8000 | 31405 |
+----+-------+---------------+------+------+------+------+-------+
Write Xlsx format to
/tmp/phpspreadsheet/04_Column_Formula_with_Totals.xlsx in 0.0121 seconds
Download 04_Column_Formula_with_Totals.xlsxPeak memory usage: 34.00390625MB