Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
mememe12
 | Femeie
23 ani
Bucuresti
cauta Barbat
23 - 80 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
IMPORTANT: For this to work properly you must not have any merged cells in the range you want to sum. There can be merged cells above or below though.
Now, say you have a table with 12 rows of data that you must sum (12 months in a year) and some of them are blanks (or have a - symbol in them). The rows are located in the 3rd column.
1. Select the entire table by clicking on the top left corner
2. Go to Insert tab -> click on Bookmark
3. Name it Table1 and click the Add button
4. Click in the cell where you want to have the sum inside the table.
5. Go to table Layout tab (appears when you are in the table).
6. Click on Formula (located at the far right corner of the tab).
7. A box will appear with a =SUM(ABOVE) formula in the Formula field.
8. Instead of ABOVE write Table1 C2:C13 (mind the space between Table1 and C2:C13). The formula from no. 7 becomes: =SUM(Table1 C2:C13)
9. Click OK. The result should be the sum of all the numbers in the column, regardless of the table headers.
IMPORTANT: Ranges set in formulas in Word are written in the same manner you would write it in Excel, except here you will have to imagine the column letters and row numbers
IMPORTANT: To edit the range in a formula you already set, you will have to first show the formula inside the cell (instead of the result), so press SHIFT+F9 to show the formula, or right click the result and select Toggle field codes. Press SHIFT+F9 again to hide the formula and show the result instead.
IMPORTANT: Whenever you edit a formula as said in the above paragraph, make sure you don't accidentally delete any spaces or { }, just replace your range.
IMPORTANT: The total (SUM) won't automatically update when you change data in the column. You will have to select the result, right click it and select Update Fields.
NOTE: You can make formulas without using the bookmark naming of the table (Table1). But using Table1 helps doing formulas in other tables from values in Table1, otherwise you can't refer to the first table in a 2nd table.
NOTE: You can simply go to go to any cell in a table, to the Table Layout tab and click on Formula and then you can insert the formula like =A2+B2^2+C2-D2 and it will do the math.
For number formatting of the result, check the Source below.
Source:
More about formulas in Word:
Modificat de TRaP (acum 4 ani)
|
|
| pus acum 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
Made it also in VBA.
I need to add the sum for multiple tables in various files for the same range of cells (C3:C14) so the snippet below helps a lot.
If I need it on another range, after inserting it I can always press SHIFT+F9 to edit it, SHIFT+F9 again to hide, then Update field with right click on the sum.
Sub Table_Sum()
Dim i As Integer
Dim oTable As Table
If Selection.Information(wdWithInTable) Then
Dim myTable As Range
Set myTable = Selection.Tables(1).Range
Else
MsgBox "You are not in a table cell, exiting Sub..."
Exit Sub
End If
For i = 1 To 100
If ActiveDocument.Bookmarks.Exists("Table" & i) = False Then
ActiveDocument.Bookmarks.Add Range:=myTable, Name:="Table" & i
Selection.InsertFormula Formula:="=SUM(Table" & i & " C3:C14)", NumberFormat:=""
Exit Sub ' exiting the For loop
End If
Next i
End Sub |
If range differs (it usually does), it can also be added with an InputBox, so the snippet above becomes:
A few notes before posting the code:
- the code will work only if you are in a table cell, otherwise it will give you a message and exit the Sub
- range is set via input box and is written just like in Excel, eg. C2:C13
- then the code first checks if there is a table with the same name and if there is it goes to the next integer number
- you can also do SUMs horizontally, eg instead of C2:C13 you can add A2:E2
- you can even sum up across multiple rows and columns, eg. A1:J10
- you can add multiple bookmarks to the same table
Sub Table_Sum()
Dim i As Integer
Dim oTable As Table
If Selection.Information(wdWithInTable) Then
Dim myTable As Range
Set myTable = Selection.Tables(1).Range
Else
MsgBox "You are not in a table cell, exiting Sub..."
Exit Sub
End If
Dim rng As String
rng = InputBox("Type the range which you want to sum (eg. C3:C14)")
For i = 1 To 100
If ActiveDocument.Bookmarks.Exists("Table" & i) = False Then
ActiveDocument.Bookmarks.Add Range:=myTable, Name:="Table" & i
Selection.InsertFormula Formula:="=SUM(Table" & i & " " & rng & ")", NumberFormat:=""
Exit Sub ' exiting the For loop
End If
Next i
End Sub |
As stated above, SUM results don't automatically update the result upon entering new data into the range, so you will have to do this manually or you can always use another button linked to the VBA code below which updates all fields in the document:
Modificat de TRaP (acum 4 ani)
|
|
| pus acum 4 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
More info / workarounds
I tried to use the { =SUM(ABOVE) } formula in a table but the answer was obviously incorrect
Better than an Easter egg: Using ToolsCalculate instead of using a formula
Create and use formulas in tables in Word
Description of Word formulas and how to use them
More on cell references in Word
_______________________________________
|
|
| pus acum 4 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
Code:
Word functions and their descriptions:
Function Description
ABS() Calculates the absolute value of the value inside the parentheses.
AND() Evaluates whether the arguments inside the parentheses are all TRUE.
AVERAGE() Calculates the average of the elements identified inside the parentheses.
COUNT() Calculates the number of elements identified inside the parentheses.
DEFINED() Evaluates whether the argument inside parentheses is defined. Returns 1 if the argument has been defined and evaluates without error, 0 if the argument has not been defined or returns an error.
FALSE Always returns 0
IF() Evaluates the first argument. Returns the second argument if the first argument is true; returns the third argument if the first argument is false. Note: Requires exactly three arguments.
INT() Rounds the value inside the parentheses down to the nearest integer.
MAX() Returns the maximum value of the items identified inside the parentheses.
MIN() Returns the minimum value of the items identified inside the parentheses.
MOD() Takes two arguments (must be numbers or evaluate to numbers). Returns the remainder after the second argument is divided by the first. If the remainder is 0 (zero), returns 0.0.
NOT Evaluates whether the argument is true. Returns 0 if the argument is true, 1 if the argument is false. Mostly used inside an IF formula.
OR() Takes two arguments. If both are false, returns 0, else returns 1. Mostly used inside an IF formula.
PRODUCT() Calculates the product of items identified inside the parentheses.
ROUND() Rounds the first argument to the number of digits specified by the second argument. If the second argument is greater than zero (0), first argument is rounded down to the specified number of digits.
If second argument is zero (0), first argument is rounded down to the nearest integer. If second argument is negative, first argument is rounded down to the left of the decimal.
SIGN() Takes one argument that must either be a number or evaluate to a number. Evaluates whether the item identified inside the parentheses if greater than, equal to, or less than zero (0).
Returns 1 if greater than zero, 0 if zero, -1 if less than zero.
SUM() Calculates the sum of items identified inside the parentheses.
TRUE Always returns 1.
The arguments can be:
Argument Description
ABOVE Cells above the current. For example, =SUM(ABOVE)
BELOW Cells below the current. For example, =SUM(BELOW)
LEFT Cells on left of the current. For example, =SUM(LEFT)
RIGHT Cells on right of the current. For example, =SUM(RIGHT) |
_______________________________________
|
|
| pus acum 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
You can't do much math in Word tables, but you can do some things.
You can do math outside of tables too, but you must bookmark tables to do that, so the formula knows which table you are referring to.
A table in Word behaves similar to cells in Excel, meaning that if you have a 10 by 10 table, you can refer to the cells in the table as A1, B3, C10 etc. and you can use these notations in formulas, eg. =A1+B1-(C1*D1)+E1^2+D1/5
It's harder to make formulas in Word and sometimes a bit tricky. You can use IF but can't put condition IF(A1=""...) because word cells are never empty, they contain an end-of-cell character. You can put condition IF(A1=0...) but if you have 0 in your cells and want to refer to blank instead of zero, this can become tricky - I haven't got a solution for this, but see topic below with my question about this matter on another forum:
In order to set numbering formats for SUM in word, or for any calculations, you can add the number format in the end of the formula, eg. for showing no decimals you can use:
=SUM(ABOVE) \# "0")
|
|
| pus acum 3 ani |
|