If I have a row of text that looks like the row below and I only in this case want to add up the b's, there my be only 1 b in the cell or 3 b's but either way I only want to total up the b's, how do I do this? If I knew how to assign a value such as b=1, bb=2, bbb=3 and then only count the b text in the row that would also work.
b
bbb
aa
c
bbb
bb
d
Total b's =9
Hi,
=SUMPRODUCT(LEN(A1:A7)-LEN(SUBSTITUTE(A1:A7,"b","")))
Regards
Thanks for the answer to my problem. This was the exact answer that I was looking for.
Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.
Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
One other question, what if there are breaks in my column that contain text and I don't want these cells to be included in the text count, how do I exclude them from being counted?
What do you mean by 'breaks'? Can you give another example, again with your desired results?
Regards
My spreadsheet is a few pages and I've posted the column heading on each page and if there are any of the same letters appear in the column heading then they will also be counted in text total. This could be an issue with how I've set up my column headings on the individual pages.
Hmmm.
You mean "a few pages" as in print pages? When you're in Page Break Preview?
You know that if it's just for printing then you can tell Excel to repeat your header from the very first sheet when printing - without having to have it physically present in the sheet at the top of each page break?
Regards
No I didn't know that and yes the breaks were only for showing the heading again. I'll take them out and set up my printing to repeat the original header.
Thanks again.
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks