VBA-Summing Data in Table in Ms word Contains Text. etc..
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
VBA-Summing Data in Table in Ms word Contains Text. etc..
hi all..
how to summing data into table with criteria:
summing with data contains text, N/A, blank,'etc..
i want to the macro whit this step:
1. select range that do you want summming .
here attacment file
any help, thanks in advance
susant
how to summing data into table with criteria:
summing with data contains text, N/A, blank,'etc..
i want to the macro whit this step:
1. select range that do you want summming .
here attacment file
any help, thanks in advance
susant
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
Why do you want to do this in Word? Excel would be more appropriate.
In what form do you want the output, and what should it look like?
In what form do you want the output, and what should it look like?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 204
- Joined: 02 Feb 2010, 23:58
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
You could use a series of formula fields, coded along the lines of:
{=SUM(B2:B6)}
Although these will update automatically when you print the document, the updates aren't dynamic as they are in Excel. Depending on what you're trying to achieve, you might do better to embed and Excel worksheet in your document or use a separate Excel workbook and insert LINK field pointing to that.
To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial, at:
https://www.msofficeforums.com/word/387 ... orial.html
or:
http://www.gmayor.com/downloads.htm#Third_party
{=SUM(B2:B6)}
Although these will update automatically when you print the document, the updates aren't dynamic as they are in Excel. Depending on what you're trying to achieve, you might do better to embed and Excel worksheet in your document or use a separate Excel workbook and insert LINK field pointing to that.
To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial, at:
https://www.msofficeforums.com/word/387 ... orial.html
or:
http://www.gmayor.com/downloads.htm#Third_party
Last edited by macropod on 29 Jul 2022, 14:18, edited 1 time in total.
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
thank you guys.
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
hi guys..
for a reason calculated must in Ms Word format..I know this not appropriate like Hans said...
but in my department for this case i must do with Ms word .this multiple pages
i found problem like this . how to summing with ignoring N/A or Blank Cell...What formula to use?
my picture just a sample.
note:
calculate for summing & calculate for average
for a reason calculated must in Ms Word format..I know this not appropriate like Hans said...
but in my department for this case i must do with Ms word .this multiple pages
i found problem like this . how to summing with ignoring N/A or Blank Cell...What formula to use?
my picture just a sample.
note:
calculate for summing & calculate for average
You do not have the required permissions to view the files attached to this post.
-
- 3StarLounger
- Posts: 204
- Joined: 02 Feb 2010, 23:58
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
Watch the video link I posted earlier.
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
yes of course but not for blank cell or n/a
-
- Administrator
- Posts: 12629
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
The obvious formula seems to ignore blank cells and N/A quite happily. Did you test this?
You do not have the required permissions to view the files attached to this post.
StuartR
-
- 3StarLounger
- Posts: 204
- Joined: 02 Feb 2010, 23:58
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
I hope the attached file will make sense.
You do not have the required permissions to view the files attached to this post.
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
The SUM(ABOVE) formula is quite unreliable for table data such as the OP supplied in post #1. This is documented and explained in my Microsoft Word Field Maths Tutorial (see link in post #4)
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]
-
- Administrator
- Posts: 12629
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
Sadly, that link doesn't work for me, it asks for a login that I don't have
StuartR
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
Using an Excel worksheet embedded in the Word document may satisfy that requirement; otherwise, the formula I provided in post #4 will provide the required summation. Averaging is simply a matter of substituting AVERAGE for SUM.Susanto3311 wrote: ↑29 Jul 2022, 07:25for a reason calculated must in Ms Word format..I know this not appropriate like Hans said...
but in my department for this case i must do with Ms word .this multiple pages
See attached demonstration of both approaches.
You do not have the required permissions to view the files attached to this post.
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
I've added a second link that doesn't require a login.
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]
-
- Administrator
- Posts: 12629
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
hi guys...thank you so much.
It's make me clear and knowing what's my problem.
It's make me clear and knowing what's my problem.
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
hi guys,,
in progress i found new problem, how to determine total average I + average II
note:
formula for average I and II is correct but for average I+II is not correct! '-last paragraph'
average I in page 8 & average II in page 9
my target in yellow shading in col. F
i have do with combination formula sum, average but not work true.
please, check in yellow shading
in progress i found new problem, how to determine total average I + average II
note:
formula for average I and II is correct but for average I+II is not correct! '-last paragraph'
average I in page 8 & average II in page 9
my target in yellow shading in col. F
i have do with combination formula sum, average but not work true.
please, check in yellow shading
You do not have the required permissions to view the files attached to this post.
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
For example:
{=AVERAGE(f5:f39,f42:f46)}
{=AVERAGE(f5:f39,f42:f46)}
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
hi paul, thanks but not true..
average I = 80
average II = 113
average total = (113+80)/2 = 96.5 --'should be'
since your formula the result is 85.5
average I = 80
average II = 113
average total = (113+80)/2 = 96.5 --'should be'
since your formula the result is 85.5
-
- 4StarLounger
- Posts: 508
- Joined: 17 Dec 2010, 03:14
Re: VBA-Summing Data in Table in Ms word Contains Text. etc..
Your 96.5 is an average of your averages, not an average of the values. 85.5 is the average of the values. Most would regard such an average of averages as a meaningless figure, since it takes no account of how many items contribute to each of the averages. Still, if that's what you want:
{=AVERAGE(C40,D47)}
or:
{=SUM(C40,D47)/2}
Note that the cell references are not to the same column due to inconsistencies in your table layout.
{=AVERAGE(C40,D47)}
or:
{=SUM(C40,D47)/2}
Note that the cell references are not to the same column due to inconsistencies in your table layout.
Paul Edstein
[Fmr MS MVP - Word]
[Fmr MS MVP - Word]