Sum the numerical values following each instance of specific text in one cell

danielb

New Member
Joined
Sep 12, 2012
Messages
18
I need help with an iteration to my existing formula where I need to add certain values within the same cell based on specific criteria.
Currently, I have cell H2 that has a plain text value like this:
GroupAct: Group Actuarial - Division~28*Group Sales Support - Division~2*Group Systems - Division~1*Group Underwriting - Division~4*Individual Life - Division~1*IT - Business Unit~1*--~3
[This is showing access group names, then a colon, then the divisions that are in that access group. The divisions are formatted by name, then a tilde, then how many people are in that division, then an asterisk, and repeat]

I then have an IF statement in cell I2 that looks for a specific text string and returns Yes or leaves the value blank:
Code:
=IF(ISNUMBER(SEARCH(": Group",H2)),"Yes",IF(ISNUMBER(SEARCH("*Group",H2)),"Yes",""))
So this returns Yes in column I for column H’s value

I am now being asked now to sum the numerical values following each instance of this specific text string being found in H2. So, in the above example:
- Find ‘: Group’ and the 28 after it
- Find the first ‘*Group’ and the 2 after it
- Find the second ‘*Group’ and the 1 after it
- Find the third ‘*Group’ and the 4 after it
- Add these values up to 35 and put that in cell I2 instead of just Yes.

I have looked at little at SUMPRODUCT, but I am not sure how that works as an array. Any help is appreciated to get me on the right track. Thanks!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
If you can use a VBA solution, here is a UDF (user defined function) that should do what you want providing your single example is representative of the text that needs to be processed...
Code:
Function GroupAdd(Text As String) As Double
  Dim X As Long, Groups() As String
  Groups = Split(Text, "Group", , vbTextCompare)
  For X = 0 To UBound(Groups)
    GroupAdd = GroupAdd + Val(Split(Groups(X) & "~", "~")(1))
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GroupAdd just like it was a built-in Excel function. For example,

=GroupAdd(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:

danielb

New Member
Joined
Sep 12, 2012
Messages
18
Thanks Rick for your quick reply! Is there a way to code this within VBA where I could run a macro myself and put focus in I2 get the result from H2 without a UDF and saving the file as a macro enabled workbook? The client I am working with will be sending me multiple xls files each week, so it may not be efficient for me to save a UDF on each file and a save it as an xlsm; instead, what I would like is when I get the xls from the client and I run a macro saved in my PERSONAL.XLSB to do the functions or formulas to get the added up counts. I can also have the macro save the formula result as text and present the client with an xls file with the new data as text, which is what they prefer (no formulas in the final version). They also are 'gun shy' about enabling macros due to malicious email attachments in phishing attempts.

Sorry I didn't think to include this information in the initial post :mad:.
 

danielb

New Member
Joined
Sep 12, 2012
Messages
18
Rick –
I figured out how to put the function in my PERSONAL.XLSB file and I use the formula “=PERSONAL.XLSB!GroupAdd.GroupAdd(H2)” and it returns a count...thank you so much for your help so far! A few follow-up questions:


  1. Does the function you wrote above look just for instances of “Group” or for “: Group” and “*Group”? There will be times where an access group name will be something like “*Individual Claims Group” and it should not count that. The function should only add up the numbers after the tilde if “: Group” or “*Group” precedes it.
  2. I need numerous other functions to look for other text strings…another example would be one that looks for “: Individual” and “*Individual”. So, I figured I would create another function called IndivAdd and I replaced certain parts of your function to this:
Code:
Function IndivAdd(Text As String) As Double
  Dim X As Long, Groups() As String
  Groups = Split(Text, "Individual", , vbTextCompare)
  For X = 0 To UBound(Groups)
    IndivAdd = IndivAdd + Val(Split(Groups(X) & "~", "~")(1))
  Next
End Function
I then use “=PERSONAL.XLSB!IndivAdd.IndivAdd(H2)” and this returns a count, but it is not correct. Like for the above example I posted, it returns a count of 29 and it should return 1.Maybe the answer to (1) will help with this too, but I wanted to mention this.

Any help you can provide would be much appreciated. Thanks!
 

danielb

New Member
Joined
Sep 12, 2012
Messages
18

ADVERTISEMENT

Rick, can you help provide any other assistance with the questions on my last post? Does anyone else have any other suggestions? Thanks.
 

danielb

New Member
Joined
Sep 12, 2012
Messages
18
I'm not sure if replying again 'bumps' this thread in the forums, but I am still in need of some assistance. I have done some online searching, but I still cannot figure the answers to my questions on the March 29th post. Any help is appreciated. Thanks.
 

Sanjeev1976

Active Member
Joined
Dec 25, 2008
Messages
257

ADVERTISEMENT

I'm not sure if replying again 'bumps' this thread in the forums, but I am still in need of some assistance. I have done some online searching, but I still cannot figure the answers to my questions on the March 29th post. Any help is appreciated. Thanks.

Try:

Function GroupAdd(Text As String) As Double
Dim X As Long, Groups() As String
Groups = Split(Text, "Individual", , vbTextCompare)
For X = 1 To UBound(Groups)
GroupAdd = GroupAdd + Val(Split(Groups(X) & "~", "~")(1))
Next
End Function
 

danielb

New Member
Joined
Sep 12, 2012
Messages
18
Sanjeev, I apologize for the lateness of my reply. Your suggestion worked perfectly...I have created numerous functions from your suggestion to look for the different text strings and count up the numbers and they work great; however, as I was implementing it, I realized that I need help with another 'wrinkle'...I have a need for some of these functions to look for 2 strings vs. 1.

So for example, I have one of the functions called 'AuditAdd' and it currently looks like the below code. In addition to finding the string "Audit" and also need it to find the string "Risk & Compliance" and add all the counts from both strings. Can you help with modifying the code to find 2 string values vs. just 1? Thanks!

Code:
Function AuditAdd(Text As String) As Double
  Dim X As Long, Groups() As String
  Groups = Split(Text, "Audit", , vbTextCompare)
  For X = 1 To UBound(Groups)
    AuditAdd = AuditAdd + Val(Split(Groups(X) & "~", "~")(1))
  Next
End Function
 

Sanjeev1976

Active Member
Joined
Dec 25, 2008
Messages
257
Thanks for your feedback.

Try :

Code:
Function AuditAdd(Text As String) As Double
  Dim X As Long, Groups() As String
  Groups = Split(Text, "Audit", , vbTextCompare)
  For X = 1 To UBound(Groups)
    AuditAdd = AuditAdd + Val(Split(Groups(X) & "~", "~")(1))
  Next
  Groups = Split(Text, "Risk & Compliance", , vbTextCompare)
  For X = 1 To UBound(Groups)
    AuditAdd = AuditAdd + Val(Split(Groups(X) & "~", "~")(1))
  Next
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,989
Members
409,553
Latest member
alscno

This Week's Hot Topics

Top