Solved

# Excel 2013 - Formula to count texts with capital letters

Posted on 2016-08-10
79 Views
Last Modified: 2016-08-10
Dear Experts,

Could you please advise about an Excel formula, which counts how many cells are in a range with only capital letters?

In the attached example the result would be 3 (SOT,WBA,HUL)

Thanks in advance,
0
Question by:csehz
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 4
5 Comments

LVL 31

Expert Comment

ID: 41751473
Assuming the strings are in the range A1:A5, then try this.....

``````=SUMPRODUCT(--(EXACT(LEFT(A1:A5,1),UPPER(LEFT(A1:A5,1)))))
``````
0

LVL 31

Accepted Solution

Subodh Tiwari (Neeraj) earned 500 total points
ID: 41751475
I think I misunderstood your question, I assumed you were trying to compare the first character of the words.

Please try this tweaked formula....

``````=SUMPRODUCT(--(EXACT((A1:A5),UPPER(A1:A5))))
``````
0

LVL 31

Expert Comment

ID: 41751478
And moreover if you want to ignore the numeric values and assuming the data is in the range A1:A6, please try this......

=SUMPRODUCT((ISTEXT(A1:A6))*(EXACT((A1:A6),UPPER(A1:A6))))
0

LVL 1

Author Closing Comment

ID: 41751479
Thank you very much for your regular help, it works of course
0

LVL 31

Expert Comment

ID: 41751482
You're welcome. Glad I could help this time also. :)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month9 days, 1 hour left to enroll

#### 738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.