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

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

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

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

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

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

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

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,

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

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!

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

You should take part in a contest for one of the greatest sites on the net. I'm going to recommend this website!

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

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

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

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

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

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

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