Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 8
Default sum cells with specific text in comments

Hello All,
I would like to sum a range of cells in a column that have specific text in the comments and display the sum at the bottom of each column. I have multiple columns i would like to add up. Is this possible using text in comments?
I have attached an example of what I would like to happen. I would like to sum all of the cells in a column with the letter "M" in the comments and place the sum at the bottom of each column as shown. I have looked at some VBA posts but they either add all the comments on the whole worksheet or find a specific text in a comment.

The other option would be to sum numbers found in the comments of a cell and display this at the bottom of each column.
Thanks for any advice,
Phil
Attached Images
 

Last edited by phillip cole : September 22nd 16 at 01:52 PM
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default sum cells with specific text in comments

Hi Phillip,

Am Thu, 22 Sep 2016 13:49:33 +0100 schrieb phillip cole:

I would like to sum a range of cells in a column that have specific text
in the comments and display the sum at the bottom of each column. I
have multiple columns i would like to add up. Is this possible using
text in comments?


try following function:

Function ComSum(myRng As Range)
Dim rngC As Range

For Each rngC In myRng
If Not rngC.Comment Is Nothing Then
If Left(rngC.Comment.Text, 1) = "M" Then
ComSum = ComSum + rngC.Value
End If
End If
Next
End Function

and call it in the sheet e.g. with
=ComSum(F2:F4)

Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Junior Member
 
Posts: 8
Default

Quote:
Originally Posted by Claus Busch View Post
Hi Phillip,

Am Thu, 22 Sep 2016 13:49:33 +0100 schrieb phillip cole:

I would like to sum a range of cells in a column that have specific text
in the comments and display the sum at the bottom of each column. I
have multiple columns i would like to add up. Is this possible using
text in comments?


try following function:

Function ComSum(myRng As Range)
Dim rngC As Range

For Each rngC In myRng
If Not rngC.Comment Is Nothing Then
If Left(rngC.Comment.Text, 1) = "M" Then
ComSum = ComSum + rngC.Value
End If
End If
Next
End Function

and call it in the sheet e.g. with
=ComSum(F2:F4)

Regards
Claus B.
--
Windows10
Office 2016


Thank you Claus,
I copied and pasted that to a module in my spreadsheet. then i set the "sum" cell at the bottom of each column to e.g. "=ComSum(AE720:AE722)". I selected the whole data table and named the range "myRng". It is definitely getting me moving in the right direction but i'm getting some funny results. sometimes its adding the comments with "M". Sometime its adding up all the letters in the cells with comments but putting it in as "MM" versus "2". Sometimes it's not returning a sum if i have letters in the cell "AT" and a comment of "M".
i have attached the results and some other information.
Thoughts?
Thank you
Attached Images
   
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default sum cells with specific text in comments

Hi Phillip,

Am Thu, 22 Sep 2016 16:29:29 +0100 schrieb phillip cole:

I copied and pasted that to a module in my spreadsheet. then i set the
"sum" cell at the bottom of each column to e.g. "=ComSum(AE720:AE722)".
I selected the whole data table and named the range "myRng". It is
definitely getting me moving in the right direction but i'm getting some
funny results. sometimes its adding the comments with "M". Sometime
its adding up all the letters in the cells with comments but putting it
in as "MM" versus "2". Sometimes it's not returning a sum if i have
letters in the cell "AT" and a comment of "M".
i have attached the results and some other information.


what exactly do you want to do?
Do you want to sum the cell values with comment text "M"? OF do you want
to count the the comments with text "M"?
Please provide an example workbook with the expectd results in it.


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Banned
 
Posts: 4
Default

hồng mạ vÃ*ng 24k golden rose (quÃ* tặng) box dùng lÃ*m quÃ* tặng cho gấu, gia đình, vợ má»›i, bạn gái Ä‘ang theo Ä‘uổi ,cặp đôi, vợ cÅ©, bạn gái cÅ©, bạn gái, vợ chồng, bạn gái má»›i, đồng nghiệp, Hoa hồng dát vÃ*ng tặng vÃ*o ngÃ*y 8/3 Hoa hong ma vang thÃ*ch hợp dÃ*nh tặng cho bạn gái những dịp 14/2 Bông hồng mạ vÃ*ng có thể dùng cho quốc tế phụ nữ 20/10

Website: http://hoahong24k.com/hoa-hong-ma-vang


  #6   Report Post  
Junior Member
 
Posts: 8
Default

Claus,
I appreciate your patience.
I have a resource tracking sheet that summarizes the resources per week and highlights any over allocations. Generally I don't need to know the specific resources for each department but I do want to know that information for my weld department. I designate welding resources by putting a number before the 'W' (#W). I have not been able to sum the numbers in front of the W. I thought I'd try using the comments for the number of welders required so then I could sum the comments. I don't really care how I do it but I would like to figure it out.
I have attached the results I desire.
You will see that I have a formula in all the rows except the 'W' row that works to add the letters in the columns. Any other suggestions would be welcome. I have tried MS Project and several other software programs and they just don't summarize all of my data like I need them to. So, Here we are. Thanks in advance. Phil



Quote:
Originally Posted by Claus Busch View Post
Hi Phillip,

Am Thu, 22 Sep 2016 16:29:29 +0100 schrieb phillip cole:

I copied and pasted that to a module in my spreadsheet. then i set the
"sum" cell at the bottom of each column to e.g. "=ComSum(AE720:AE722)".
I selected the whole data table and named the range "myRng". It is
definitely getting me moving in the right direction but i'm getting some
funny results. sometimes its adding the comments with "M". Sometime
its adding up all the letters in the cells with comments but putting it
in as "MM" versus "2". Sometimes it's not returning a sum if i have
letters in the cell "AT" and a comment of "M".
i have attached the results and some other information.


what exactly do you want to do?
Do you want to sum the cell values with comment text "M"? OF do you want
to count the the comments with text "M"?
Please provide an example workbook with the expectd results in it.


Regards
Claus B.
--
Windows10
Office 2016
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default sum cells with specific text in comments

Hi Phillip,

Am Tue, 27 Sep 2016 16:04:09 +0100 schrieb phillip cole:

I have a resource tracking sheet that summarizes the resources per week
and highlights any over allocations. Generally I don't need to know the
specific resources for each department but I do want to know that
information for my weld department. I designate welding resources by
putting a number before the 'W' (#W). I have not been able to sum the
numbers in front of the W. I thought I'd try using the comments for the
number of welders required so then I could sum the comments. I don't
really care how I do it but I would like to figure it out.
I have attached the results I desire.
You will see that I have a formula in all the rows except the 'W' row
that works to add the letters in the columns. Any other suggestions
would be welcome. I have tried MS Project and several other software
programs and they just don't summarize all of my data like I need them
to. So, Here we are. Thanks in advance. Phil


the link for the attached workbook is missing.
Have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Tracking"
and download the workbook because macros are disabled in OneDrive.


Regards
Claus B.
--
Windows10
Office 2016
  #8   Report Post  
Junior Member
 
Posts: 8
Default

Clause,
Thank you that is very helpful.
How do I ignore anything that follows the 'W' ?
Please see attached spreadsheet that shows a 'value' error when i add a ', C' after the 'W'.
Thank you,



Quote:
Originally Posted by Claus Busch View Post
Hi Phillip,

Am Tue, 27 Sep 2016 16:04:09 +0100 schrieb phillip cole:

I have a resource tracking sheet that summarizes the resources per week
and highlights any over allocations. Generally I don't need to know the
specific resources for each department but I do want to know that
information for my weld department. I designate welding resources by
putting a number before the 'W' (#W). I have not been able to sum the
numbers in front of the W. I thought I'd try using the comments for the
number of welders required so then I could sum the comments. I don't
really care how I do it but I would like to figure it out.
I have attached the results I desire.
You will see that I have a formula in all the rows except the 'W' row
that works to add the letters in the columns. Any other suggestions
would be welcome. I have tried MS Project and several other software
programs and they just don't summarize all of my data like I need them
to. So, Here we are. Thanks in advance. Phil


the link for the attached workbook is missing.
Have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Tracking"
and download the workbook because macros are disabled in OneDrive.


Regards
Claus B.
--
Windows10
Office 2016
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default sum cells with specific text in comments

Hi Phillip,

Am Tue, 27 Sep 2016 18:05:07 +0100 schrieb phillip cole:

How do I ignore anything that follows the 'W' ?
Please see attached spreadsheet that shows a 'value' error when i add a
', C' after the 'W'.


there is still no link.
Insert a space between the number and the text.
Have another look for the workbook at OneDrive


Regards
Claus B.
--
Windows10
Office 2016
  #10   Report Post  
Banned
 
Posts: 5
Default

Quote:
Originally Posted by phillip cole View Post
Hello All, I would like to sum a range of cells in a column that have specific text in the comments and display the sum at the bottom of each column. I have multiple columns i would like to add up. Is this possible using text in comments? I have attached an example of what I would like to happen. I would like to sum all of the cells in a column with the letter "M" in the comments and place the sum at the bottom of each column as shown. I have looked at some VBA posts but they either add all the comments on the whole worksheet or find a specific text in a comment. The other option would be to sum numbers found in the comments of a cell and display this at the bottom of each column. Thanks for any advice, Phil
Fantastic post however , I was wanting to know if you could write a litte more on this topic? I'd be very grateful if you could elaborate a little bit more. Cheers!


  #11   Report Post  
Banned
 
Posts: 5
Default

Quote:
Originally Posted by phillip cole View Post
Hello All, I would like to sum a range of cells in a column that have specific text in the comments and display the sum at the bottom of each column. I have multiple columns i would like to add up. Is this possible using text in comments? I have attached an example of what I would like to happen. I would like to sum all of the cells in a column with the letter "M" in the comments and place the sum at the bottom of each column as shown. I have looked at some VBA posts but they either add all the comments on the whole worksheet or find a specific text in a comment. The other option would be to sum numbers found in the comments of a cell and display this at the bottom of each column. Thanks for any advice, Phil
Thanks a bunch for sharing this with all of us you actually realize what you are speaking about! Bookmarked. Please additionally talk over with my site =). We will have a hyperlink change agreement between us
  #12   Report Post  
Banned
 
Posts: 5
Default

Quote:
Originally Posted by phillip cole View Post
Hello All, I would like to sum a range of cells in a column that have specific text in the comments and display the sum at the bottom of each column. I have multiple columns i would like to add up. Is this possible using text in comments? I have attached an example of what I would like to happen. I would like to sum all of the cells in a column with the letter "M" in the comments and place the sum at the bottom of each column as shown. I have looked at some VBA posts but they either add all the comments on the whole worksheet or find a specific text in a comment. The other option would be to sum numbers found in the comments of a cell and display this at the bottom of each column. Thanks for any advice, Phil
You should take part in a contest for one of the greatest sites on the net. I'm going to recommend this website!
  #13   Report Post  
Junior Member
 
Posts: 8
Default

I'm getting close! I can feel it!
I added a space between the number and the letter 'W'.
I am able to hit shift ctrl enter and have excel turn the formula into an array but it's still displaying "#value".

When I evaluate the formula the Error in Value is "A value used in the formula is of the wrong data type." Is this a formatting issue?

I finally realized I couldn't upload an .xlsm file. I've uploaded a zip file.

Thanks,
Phil


Quote:
Originally Posted by Claus Busch View Post
Hi Phillip,

Am Tue, 27 Sep 2016 18:05:07 +0100 schrieb phillip cole:

How do I ignore anything that follows the 'W' ?
Please see attached spreadsheet that shows a 'value' error when i add a
', C' after the 'W'.


there is still no link.
Insert a space between the number and the text.
Have another look for the workbook at OneDrive


Regards
Claus B.
--
Windows10
Office 2016
Attached Files
File Type: zip TEST 20160929.zip (13.3 KB, 56 views)
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default sum cells with specific text in comments

Hi Phillip,

Am Wed, 28 Sep 2016 18:32:24 +0100 schrieb phillip cole:

I'm getting close! I can feel it!
I added a space between the number and the letter 'W'.
I am able to hit shift ctrl enter and have excel turn the formula into
an array but it's still displaying "#value".

When I evaluate the formula the Error in Value is "A value used in the
formula is of the wrong data type." Is this a formatting issue?


that is because you have blank cells and cells with only one character.
Then FIND returns an error.
Have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "TEST 20160929"


Regards
Claus B.
--
Windows10
Office 2016
  #15   Report Post  
Junior Member
 
Posts: 8
Default

Really? Just "=sumw(range)" ?
That's Fantastic!
What is that doing to work the sum out? What does sumw do?



Quote:
Originally Posted by Claus Busch View Post
Hi Phillip,

Am Wed, 28 Sep 2016 18:32:24 +0100 schrieb phillip cole:

I'm getting close! I can feel it!
I added a space between the number and the letter 'W'.
I am able to hit shift ctrl enter and have excel turn the formula into
an array but it's still displaying "#value".

When I evaluate the formula the Error in Value is "A value used in the
formula is of the wrong data type." Is this a formatting issue?


that is because you have blank cells and cells with only one character.
Then FIND returns an error.
Have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "TEST 20160929"


Regards
Claus B.
--
Windows10
Office 2016


  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default sum cells with specific text in comments

Hi Phillip,

Am Wed, 28 Sep 2016 21:37:19 +0100 schrieb phillip cole:

Really? Just "=sumw(range)" ?
That's Fantastic!
What is that doing to work the sum out? What does sumw do?


press Alt+F11. There is an UDF that looks for the cells with a W in it
and summarize the values in front of the "W".

Regards
Claus B.
--
Windows10
Office 2016
  #17   Report Post  
Junior Member
 
Posts: 8
Default

Claus,
I can't tell you how thankful I am for your help. This has been bugging me for quite a few weeks (I'm not an excel expert) and now I can finally put it to rest. I now have a resource tracking sheet that can tell me in a glance if I am over allocated. I appreciate your expertise so much.
I have posted the final example sheet for others to see if needed.
Thanks again,
Phil



Quote:
Originally Posted by Claus Busch View Post
Hi Phillip,

Am Wed, 28 Sep 2016 21:37:19 +0100 schrieb phillip cole:

Really? Just "=sumw(range)" ?
That's Fantastic!
What is that doing to work the sum out? What does sumw do?


press Alt+F11. There is an UDF that looks for the cells with a W in it
and summarize the values in front of the "W".

Regards
Claus B.
--
Windows10
Office 2016
Attached Files
File Type: zip RESOURCE TRACKING SPREADSHEET FINAL.zip (13.9 KB, 41 views)
  #18   Report Post  
Junior Member
 
Posts: 8
Default

Claus,
That is working great but now i've been asked to put hours (1-1000) in versus just resources (1-9) and the vba doesn't want to pick up the larger numbers. I tried changing some of the vba you gave me but i'm not having any luck. I thought it found the numeric values left of the space and returned it. Is that correct? Here is your vba that I have in there right now.

Function SumW(myRng As Range) As Long
Dim rngC As Range

For Each rngC In myRng
If InStr(rngC, "W") And IsNumeric(Left(rngC, InStr(rngC, " "))) Then
SumW = SumW + CInt(Left(rngC, 1))
End If
Next
End Function

I'm thinking I need to determine the length of the string left of the " " and then convert that to an integer and sum them up. Is that correct?

Thanks again,
Phil

Quote:
Originally Posted by Claus Busch View Post
Hi Phillip,

Am Wed, 28 Sep 2016 21:37:19 +0100 schrieb phillip cole:

Really? Just "=sumw(range)" ?
That's Fantastic!
What is that doing to work the sum out? What does sumw do?


press Alt+F11. There is an UDF that looks for the cells with a W in it
and summarize the values in front of the "W".

Regards
Claus B.
--
Windows10
Office 2016
Attached Files
File Type: zip RESOURCE TRACKING SPREADSHEET FINAL.zip (13.9 KB, 37 views)
  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default sum cells with specific text in comments

Hi Phillip,

Am Thu, 6 Oct 2016 13:26:29 +0100 schrieb phillip cole:

That is working great but now i've been asked to put hours (1-1000) in
versus just resources (1-9) and the vba doesn't want to pick up the
larger numbers. I tried changing some of the vba you gave me but i'm
not having any luck. I thought it found the numeric values left of the
space and returned it. Is that correct? Here is your vba that I have
in there right now.


try:
Function SumW(myRng As Range) As Long
Dim rngC As Range

For Each rngC In myRng
If InStr(rngC, "W") And IsNumeric(Left(rngC, InStr(rngC, " "))) Then
SumW = SumW + CInt(Left(rngC, InStr(rngC, " ") - 1))
End If
Next
End Function

If that doesn't work for you provide a sample workbook that I can see
the data.


Regards
Claus B.
--
Windows10
Office 2016
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can we copy cells comments text and paste to cells שי פלד Excel Discussion (Misc queries) 3 December 12th 05 06:16 AM
Text disappearing from cells and comments? tlkcpa Excel Discussion (Misc queries) 1 December 7th 05 08:13 AM
UDF code to find specific text in cell comments, then average cell values bruch04 Excel Programming 3 December 5th 05 11:01 PM
Comments with text from cells coco Excel Programming 4 October 5th 03 11:59 AM
Add Comments with Text From Cells coco Excel Programming 1 October 4th 03 08:10 PM


All times are GMT +1. The time now is 08:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"