
Count And Sum Strings In Individual Cell


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Count And Sum Strings In Individual Cell  Excel

View Answers


I have cells that contain a string like the sample below.
I'm trying to get two totals from each cell. One is the count of each 7 digit number(count the commas?) and the second is the Sum of the numbers in the parenthesis.
Thanks for any help
0002438 (4), 0003517 (3), 0004655 (1), 0005207 (3), 0009100 (3), 0361394 (1), 0376921 (2), 0405993 (4), 0577734 (2), 0660381 (2), 0785188 (1), 0941872 (2)
Similar Excel Video Tutorials
Formula Or Function Confusion
 Learn about the differences and similarities between a formula and a function! See: 1) definitions for formulas and functions; 2) Math formulas; 3) Te ...
Similar Topics
Here are five sample cell entries:
G2 = 4
J2 = Blank (Empty)
M2 = 13,19,20
P2 = 2,10
S2 = 29
always numbers, separated by commas if more than one entry
I need to count the number of numbers
in the 5 sample cells listed above the count would be 7
there are actually 12 cells in each range to be counted and as in the sample they are 3 columns separated. I could revamp the entire spreadsheet to make them adjacent if necessary but I want to change the users spreadsheet as little as possible. I'm only trying to automate some data entry for her.
I need to be able to count alphas and digits in a cell. I have a column of phone numbers, and I need a quick and easy way to find out which ones are just extensions, which are seven digit phone numbers, and which are full 10 digit or more numbers. Some of these cells have an asterisk before the number, which indicated we were dialing voicmail. I just need to be able to distinguish which ones I need to count as long distance numbers. Thanks!
I have a cell that collects 10 digit strings from other worksheets. How can I check that string and add a comma and 1 space after every 10 digits? Sometimes there is 2,3 or more spaces already between the 10 digit string, so how could I remove those also and just be left with a string of digits 10 characters long with a comma and space after each.
For example: (I'll use numbers as text to make it easier to count)
Change: 12345678901234567890 1234567890 1234567890
To: 1234567890, 1234567890, 1234567890
I don't want a comma or space after the final digit in the string.
Thanks in advance,
Calvin
I have a sheet which holds numbers including decimal points, there are groups of numbers which I want to count if they are between certain numbers, is there a Function that will do this.
Sample
A1 has number say 2
A2 has number say 4
A3 has number say 2.33
A4 has number say 10
Totals
I want to beable to count all the numbers between 1 and 3
I want to beable to count all the numbers between 4 and 7
I want to beable to count all the numbers between 8 and 11
Good afternoon all.
I am trying to count records in a sheet.
I put in =count(<<range>>) and I only get back 2, even though there are more. The only significance in the number 2 that i can think of is that there are two records which are numbers only. The others are text strings.
Is count only for numbers? if so, what can i use to count the strings as well?
Hello again,
The problem I am having deals with strings.
I am trying to...
Return a count for the most popular string in a row.
Return that string in the adjacent cell the the count.
I tried to use sumproduct, because I initially only wanted to use strings that were present in every cell. I have since changed my mind and would like to just count the majority string and return that string.
Example is attached.
Thanks,
Joe!
Is there a way to count a set string in a number of cells and display the number?
Visual example (this is all in the same row, commas separate columms)
111(1) 132(2), 144(1) 221(2), 442(1) 444(1), 134(1) 124(2)
I would like a cell to display how many '(2)'s there are.
Does anyone know how I can count the number of cells whose 5th and 6th digits include a certain number? That is, how many cells include "31" in the 5th and 6th digit location among "9001311, 9001313, 9823317, 9823328, etc..." I see ways to use countif for the same situation if the cell contains text, but am not sure how to get it to work for numbers.
Thanks!
Hi, probably a very easy one here, im looking to count the unique number of values within a single cell string which are separated by commas
example:
Cell A1 contains: 1,2,3A33,444,2,1,16
The resulting formula would give a count of 5 unique value within cell A1 as values '2' and '1' are repeated twice within the cell and would therefore be excluded.
Any help would be most appreciated, thank you!
Hi,
I'm trying to find a better (smarter) way of extracting a 9 digit number from a string.
The number can start anywhere within the string, and the string may contain other numbers (though not immediately adjacent to the 9 digit number).
I wrote a clumsy macro to text each character (from left to right) to see if it is a number (using a 'if variable >=chr(48) and variable <=chr(57) test) and then tested each of the following 8 characters. Once I established that I had a group of 9 numbers in the string then put the 9 numbers into an adjacent cell.
Then looped through the column with the strings.
I'm thinking there is a better way to do this and any suggestions / pointers most appreciated.
Good Afternoon,
I'm writing a little program for my school and I've run into a small problem.
The code in question is:
Code:
Range(Cells(19, 1), Cells(19, 3)).Copy
Range(Cells(19 + Count, 1), Cells(19 + Count, 3)).PasteSpecial (xlPasteFormats)
Cells(19 + Count, 1).Value = Cells(18 + Count, 1).Value + 1
Cells(19 + Count, 2).Value = CompleteString
Cells(19 + Count, 3).Formula = "(this is where I'm stuck)"
Where I've written "this is where I'm stuck" I'd like to have a reference to another cell, it will just duplicate the value in that cell by referencing it. However, I cannot figure out how to do that.
I don't think I'm being clear enough, so I'll give you an example. Other code in my project adds a preformatted area where a user would input a number of values and it would sum them in a "totals" cell underneath the area. The cell I am stuck on will replicate this value in another part of the sheet.
I've tried, foolishly, I discovered,
Code:
Cells(19 + Count, 3).Formula = "=Cells(15 + (Count * 8), 11)"
but that obviously didn't work. (15 + (Count * 8), 11) is the location of the cell that I want to reference at all times. As I add areas, count increases.
This code is supposed to create a unique identifier consisting of the first 4 letters of the activesheet name and then a unique number that starts with 0001 and counts up. For some reason, I have been receiving complaints from users of duplicate numbers. I cannot seem to recreate these problems, but have seen them. Does anyone know why this code is not working properly? Thanks!
Code:
Sub Casegenerator()
Prefix = Strings.Left(ActiveSheet.Name, 4)
Count = 0
For i = 1 To Cells(Rows.Count, "E").End(xlUp).Row
If Strings.Left(Cells(i, "E"), 4) = Prefix Then Count = Count + 1
Next i
CaseCount = Count + 1
Cells(2, "E").Value = Prefix & "" & Format(CaseCount, "0000")
End Sub
Sorry about the blank post.
I am trying to come up with a way to count individual digits i a list.
I have 2 list in column b & c that go from row 1  38. I need to know how to
count the total number of times each digit, 0,1,2,3,etc, appears in the list.
Is there a function that will do this?
Thank you,

James Bonds
Excel Learner
I have a column of numbers. I want to count the number of numbers in that column and display the count in a certain cell. I only want it to count the numbers if it is acutally a number.
=COUNTIF(B2:B30,">100")
I'm assuming that this will give me a count of how many numbers appear within the selected cells. In my sample there are 28 different numbers (all under 100) so I want the number 28 to appear in the cell with the COUNTIF statement.
The Problem: Nothing appears in the cell with the COUNTIF statement except the COUNTIF statement. Meaning that I acutally see the COUNTIF statement in the cell.
What am I doing wrong?
Hi,
Using VBA, I'm trying to count how many times the following strings appear on the the report:
BTC, WD, ABC, XYZ
The challenge is the cell may contain both 'stand alone' or combination of those characters..so something like this:
 BTC, Wd, abcblablaba, xyz
 btC, wd
 klm, SC, WD
 blbalbalXYZ
 blablabaxyz
 xyz,abc, btc
 XYT, XYZ, BTC
In this example, the total count a
BTC: 4
WD: 3
ABC: 2
XYZ: 5
So whether it's standalone or combination, mixed upper/lowercase, spaces in between... I just want to get a count of how many times that those specific character appear, i.e:
 blbalbalXYZ > count as 1 XYZ
 xyz > count as 1 XYZ
 ...., xyz > count as 1 XYZ
so doesn't matter how/what/where the combination are, as long as that specific character appears, then it gets counted.
Hello,
I have one problem. I have string cells in column O that contains numbers. Like O1 "D 56' +3'", O2 "D 56'" and some cells are empty. So i want to count how many cells are in the column O that contains exactly "D (any number)'" or "D (any number)' +(any number)'". I tryed to use COUNTIF function and used * to identify any number, but that doesnt work because it counts all strings that contains "D (any number)'" and "D (any number)' +(any number)'". How can i do this?
Hi,
I am stuggling with the following scenario.
One ROW contains repeated Strings and another ROW contains the corresponing Value for that String.
EG:
ROW 1: a b b c a c
ROW 2: 5 6 9 2 2 3
I want to count the number of times a particular sting is repeated in ROW 1 and average out the corresponding values for that string in ROW 2.
Above EG: a has come twice and avg is 3.5, b has come twice and avg is 7.5, etc...
Count I can get, which is pretty simple. But its the avg of the corresponding value, which I am unable to figure out...
Please help...thanx
I have this function that takes in a string of words separated by commas. In this case it is:
"Production, Production Goals, Daily Cost, Total Downtime"
Forgoing the encasing quotes, the commas, by my count are at characters' 11, 29, and 41.
The code below should get each word by itself with no spaces or commas showing, but when I run the code. I get for each iteration (both the starting and ending character count and the extracted string:
1st: 1, 11, sExtract = "Production"
How do you count the number of text strings there are in a cell seperated by commas
Example:
cell a1= football,baseball,soccer
The result should be 3
cell a2 = football
The result should be 1
cell a3 = baseball,hockey,tennis,baskeball
The result should be 4
Thanks
I know that the answer is probably simple, but I would like a formula that:
1. Counts the number of cells in a column with the same 18character text string(Full String is 24) using a RIGHT( function.
2. Returns a 1 if there is 1 and a zero if the count is greater than 1.
Thanks,
Thomas
Hi,
How to write a formula that reads numbers in C20:G20 to see if there is a match in C21:Q21? I want to put the formula on a different cell to see how the match count. If there is a duplicate match, only count as one, it only count if it's a unique matching digit.
For an example:
C20 has 05
D20 has 07
E20 has 11
F20 has 34
G20 has 37
then there are bunch of numbers between C21:Q21
4 5 34 38 41 5 6 35 39 42 3 37 33 37 40
Then the formula only count these as 3 match, not 5 because digit 5 and 37 are duplicate and can only be count once.
Thank you for your help!
xoxo
xl2000
Been trying for hours  close but can't get it. Weekend VBA'er at best. Help?
I need to count only the # of rows on my sheet in which col I contains a digit within a string of alpha numeric data entered in each cell in "I". Number of total populated rows varies up to about 100.
Column "I" raw data on a typical day: (code snippet below)
2
1
1
1HP
1PC
2Q
4
4C
O
2Z
1J
A
2
4X
1
L
1
3K
3JZ
3
4
Sheet will be sorted by Column "I" PRIOR TO running my code, so I was trying to use a variable to step up through "I" and return the row # of the first cell found with a digit in the string. This number will then be used in further steps of the routine.
My code snippet so far:
Code:
Dim x As Integer
LR = Sheet1.Cells(Rows.Count, 9).End(xlUp).Row
For c = LR To 1 Step 1
If Range("I" & c) Like "*#*" Then
x = Range("I" & c).Row
End If
Next c
I'm not sure if the syntax using Like is correct (I'll use anything suggested) and I think my loop is wrong/used incorrectly.
Thanks if any care to help.
Chris
This is a sample of the code I have in my module:
Code:
If Range("AC4").Value = 1 And Range("AF4").Value "" And Range("AF4").Value < Range("AC9").Value Then
If Not Intersect(Range("F5"), Target) Is Nothing Then
Cells(Rows.Count, "AF").End(xlUp).Offset(1).Value = Range("F5").Value
Range("AF5").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(2, 0).Value
Range("AF6").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(3, 0).Value
Range("AF7").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(4, 0).Value
Range("AF8").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(5, 0).Value
Range("AF9").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(6, 0).Value
Range("AF10").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(7, 0).Value
Range("AF11").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(8, 0).Value
Range("AF12").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(9, 0).Value
Range("AF13").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(10, 0).Value
Range("AF14").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(11, 0).Value
Range("AF15").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(12, 0).Value
Range("AF16").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(13, 0).Value
Range("AF17").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(14, 0).Value
Range("AF18").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(15, 0).Value
Range("AF19").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(16, 0).Value
Range("AF20").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(17, 0).Value
Range("AF21").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(18, 0).Value
Range("AF22").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(19, 0).Value
Range("AF23").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(20, 0).Value
Range("AF24").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(21, 0).Value
Range("AF25").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(22, 0).Value
Range("AF26").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(23, 0).Value
Range("AF27").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(24, 0).Value
Range("AF28").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(25, 0).Value
Range("AF29").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(26, 0).Value
Range("AF30").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(27, 0).Value
Range("AF31").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(28, 0).Value
Range("AF32").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(29, 0).Value
Range("AF33").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(30, 0).Value
Range("AF34").Value = Cells(Rows.Count, "AF").End(xlUp).Offset(31, 0).Value
End If
End If
If Range("AC4").Value = 1 And Range("AG4").Value "" And Range("AG4").Value < Range("AC9").Value Then
If Not Intersect(Range("F6"), Target) Is Nothing Then
Cells(Rows.Count, "AG").End(xlUp).Offset(1).Value = Range("F6").Value
Range("AG5").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(2, 0).Value
Range("AG6").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(3, 0).Value
Range("AG7").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(4, 0).Value
Range("AG8").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(5, 0).Value
Range("AG9").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(6, 0).Value
Range("AG10").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(7, 0).Value
Range("AG11").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(8, 0).Value
Range("AG12").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(9, 0).Value
Range("AG13").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(10, 0).Value
Range("AG14").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(11, 0).Value
Range("AG15").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(12, 0).Value
Range("AG16").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(13, 0).Value
Range("AG17").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(14, 0).Value
Range("AG18").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(15, 0).Value
Range("AG19").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(16, 0).Value
Range("AG20").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(17, 0).Value
Range("AG21").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(18, 0).Value
Range("AG22").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(19, 0).Value
Range("AG23").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(20, 0).Value
Range("AG24").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(21, 0).Value
Range("AG25").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(22, 0).Value
Range("AG26").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(23, 0).Value
Range("AG27").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(24, 0).Value
Range("AG28").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(25, 0).Value
Range("AG29").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(26, 0).Value
Range("AG30").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(27, 0).Value
Range("AG31").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(28, 0).Value
Range("AG32").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(29, 0).Value
Range("AG33").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(30, 0).Value
Range("AG34").Value = Cells(Rows.Count, "AG").End(xlUp).Offset(31, 0).Value
End If
It works fine but it is slow, and I have lots & lots of it repeated for cells AF to BS.
Is there a better way of coding?
What I am trying to do is count a colume of numbers (# of Checks  B) "except" when there are no numbers (Account #'s  D) to get a "total number of deposits made" for the month.
I am using named ranges and the formula =COUNT(Totals) gives me a close number but incorrectly includes the daily item counts. (each sheet calculates an entire month and the line that includes no Account Number sums the daily totals.
Countif does not allow for enough aurguments  at least from what I can tell.
The if statement below doesn't work if it is in row #1  where I need to place it, but does work in a row that contains the data (row 2). Unfortunetly, it still gives me the same "Deposit Count" as above.
=IF(Account>0,COUNT(Totals),"")
I am trying to do this so no additional colume needs to be added to the worksheet. What am I missing?
Thanks!
I need to count one number in a number sequence for cells a1:a20. Im trying
to count the number "2" in every cell, but every cell has 4 numbers. How do I
count only one of those numbers in every cell?

