Excel 2013 - Formula to count texts with capital letters

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)

Data
Thanks in advance,
LVL 1
csehzIT consultantAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
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))))

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Assuming the strings are in the range A1:A5, then try this.....

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

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
csehzIT consultantAuthor Commented:
Thank you very much for your regular help, it works of course
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could help this time also. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.