Question description:
This user has given permission to use the problem statement for this
blog.
I'm wondering if there is a excel formula for counting characters in a column up to a certain amount
Solved by S. S. in 34 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/05/2018 - 03:14
Hello
Excelchat Expert
09/05/2018 - 03:18
Can you tell me more? And/or show me some example?
Excelchat Expert
09/05/2018 - 03:20
Hello!
Excelchat Expert
09/05/2018 - 03:22
Excel count formula counts number of characters in a cell.
User
09/05/2018 - 03:27
hi there sorry for the delay
Excelchat Expert
09/05/2018 - 03:27
not a problem.
User
09/05/2018 - 03:27
i'm just reading your notes
Excelchat Expert
09/05/2018 - 03:27
hmm.
Excelchat Expert
09/05/2018 - 03:28
And?
User
09/05/2018 - 03:28
here is an example
User
09/05/2018 - 03:29
in column A, i would like to count how many "x" are there
Excelchat Expert
09/05/2018 - 03:29
Okay, then we use countif function.
User
09/05/2018 - 03:29
however if more than 5, i dont need to count more
User
09/05/2018 - 03:29
is there a way to make this stop at 5
Excelchat Expert
09/05/2018 - 03:29
What would be the result then?
Excelchat Expert
09/05/2018 - 03:29
Yeah, there is.
User
09/05/2018 - 03:30
if it helps, i can send you the link to the document im working on
User
09/05/2018 - 03:30
what happens after 4 min? I have a count down timer going
Excelchat Expert
09/05/2018 - 03:31
You can extend the time.
User
09/05/2018 - 03:31
perfect
Excelchat Expert
09/05/2018 - 03:31
You will be asked for your choice.
Excelchat Expert
09/05/2018 - 03:31
So, in B1 I have wrote this formula. =IF(COUNTIF(A:A,"*x*")>5,5,COUNTIF(A:A,"*x*"))
User
09/05/2018 - 03:32
here is the link for the actual sheet i would like to apply the formula to
Excelchat Expert
09/05/2018 - 03:32
This will give you a count of x in column A, but if the count is more than 5, then would show 5.
User
09/05/2018 - 03:32
https://docs.google.com/spreadsheets/d/1ZcaepBfXHWGiHioLpshWndvYhMYO9aIKFE2dRYRcd0s/edit#gid=1119552089
User
09/05/2018 - 03:32
can we bring this up ono the right
Excelchat Expert
09/05/2018 - 03:32
I'm really sorry, external link are not allowed on our rules.
User
09/05/2018 - 03:32
no problem!
Excelchat Expert
09/05/2018 - 03:32
Can you please paste data on the right?
User
09/05/2018 - 03:32
yep
User
09/05/2018 - 03:32
one
Excelchat Expert
09/05/2018 - 03:32
thanks.
User
09/05/2018 - 03:33
there you go
Excelchat Expert
09/05/2018 - 03:34
So, what do you need here?
User
09/05/2018 - 03:34
I would like the total points to date to display in G 21
User
09/05/2018 - 03:35
i need to count the number of x in each team members section but not exceed 25 per section or 5 per coloum in each seaction
User
09/05/2018 - 03:35
does that make sense
Excelchat Expert
09/05/2018 - 03:35
total point is total number of X in B8:Z18 range?
User
09/05/2018 - 03:36
yes, however, if someone records more than 5 x per day for example Janis, on monday, i do not want the formula to count the extra x's
Excelchat Expert
09/05/2018 - 03:38
Okay, then we need several countif formula. To be exact, one countif for each column, then sum the results for total.
Excelchat Expert
09/05/2018 - 03:38
I'm showing an example. 1 minute please.
User
09/05/2018 - 03:38
that being said, total points to date for this week for all team members should only be 122 (which i had to manually count leaving out all the "extra" x that were recorded on certain days
User
09/05/2018 - 03:38
yep perfect
User
09/05/2018 - 03:39
that's what i was thinking
Excelchat Expert
09/05/2018 - 03:41
=IF(COUNTIF($B$8:$B$18,"=x")>5,5,COUNTIF($B$8:$B$18,"=x"))
Excelchat Expert
09/05/2018 - 03:41
Now, this is for column B.
Excelchat Expert
09/05/2018 - 03:41
And this is for column C. IF(COUNTIF($C$8:$C$18,"=x")>5,5,COUNTIF($C$8:$C$18,"=x"))
User
09/05/2018 - 03:42
so i would need to write that formula for each column then sum the columns?
Excelchat Expert
09/05/2018 - 03:43
Please see row 19
Excelchat Expert
09/05/2018 - 03:44
This would be the total for each day. Then in G21, =SUM(B19:Z19) will give you the total count.
User
09/05/2018 - 03:44
amazing!
Excelchat Expert
09/05/2018 - 03:44
So, how do you like this service?
User
09/05/2018 - 03:45
outstanding
Excelchat Expert
09/05/2018 - 03:45
We will appreciate your feedback.
User
09/05/2018 - 03:45
you helped me quickly and super efficiently
Excelchat Expert
09/05/2018 - 03:45
And it would be amazing if you also visit us in future and refer this site to Friends and Family.
User
09/05/2018 - 03:45
thank you so much
User
09/05/2018 - 03:45
is it always free?
Excelchat Expert
09/05/2018 - 03:45
Glad to be able to help.
User
09/05/2018 - 03:46
i absolutely will!
Excelchat Expert
09/05/2018 - 03:46
You have to buy session but there is always some promotions going.
User
09/05/2018 - 03:46
is this one free because its first time
Excelchat Expert
09/05/2018 - 03:46
yeah, that's correct.
Excelchat Expert
09/05/2018 - 03:47
anything else?
User
09/05/2018 - 03:47
nope! What did you say your name was
Excelchat Expert
09/05/2018 - 03:48
Unfortunately we are not allowed to reveal personal information, company rules for discretion.
User
09/05/2018 - 03:48
i get it
User
09/05/2018 - 03:48
no problem!
Excelchat Expert
09/05/2018 - 03:48
Thanks.
Excelchat Expert
09/05/2018 - 03:48
I believe there is a close button on your side.
User
09/05/2018 - 03:48
whatever your name is....Thanks for all your help!
User
09/05/2018 - 03:48
sounds good take care
Excelchat Expert
09/05/2018 - 03:48
Thanks and have a great day.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.