Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 Last edited by phillip cole : September 22nd 16 at 01:52 PM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
Quote:
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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:
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
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:
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
Quote:
|
#11
|
|||
|
|||
Quote:
|
#12
|
|||
|
|||
Quote:
|
#13
|
|||
|
|||
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:
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
Really? Just "=sumw(range)" ?
That's Fantastic! What is that doing to work the sum out? What does sumw do? Quote:
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
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:
|
#18
|
|||
|
|||
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:
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can we copy cells comments text and paste to cells | Excel Discussion (Misc queries) | |||
Text disappearing from cells and comments? | Excel Discussion (Misc queries) | |||
UDF code to find specific text in cell comments, then average cell values | Excel Programming | |||
Comments with text from cells | Excel Programming | |||
Add Comments with Text From Cells | Excel Programming |