Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc